Find 'carriage return' in field

[Craig Roberts]

Hi all

This is a two-parter.

1) First, can I use the FIND forumla to look for a carriage return or
newline character in a single cell of multi-lined text imported from another
program?

Cell B1 would contain following formula: =LEFT(A1,FIND(<CR>, A1)), where CR
is carriage return character.

2) I often analyse filepaths and need to calculate their constituent parts
of folders and filename, e.g. in "/Macintosh HD/Users/Documents/Folder
A/Folder B/Folder C/..../filename" I might want to know Folder A and Folder
B, and the filename.

For this, I would use the formula in part 1 above to determine position of
"/" and then in cell C1 would then put formula: =RIGHT(A1,LEN(A1)-FIND(<CR>,
A1)), to calculate the remaining path.
Then I would calculate the next folder and the right hand side of path after
that...and so on.

This manual recursion is long-winded so I was wondering if there an easier
way to do this, perhaps with lists?


Regards
Craig Roberts.

 

[JE McGimpsey]

Hi all

This is a two-parter.

1) First, can I use the FIND forumla to look for a carriage return or
newline character in a single cell of multi-lined text imported from another
program?

Cell B1 would contain following formula: =LEFT(A1,FIND(<CR>, A1)), where CR
is carriage return character.

One way:

=LEFT(A1,FIND(CHAR(13),A1))

2) I often analyse filepaths and need to calculate their constituent parts
of folders and filename, e.g. in "/Macintosh HD/Users/Documents/Folder
A/Folder B/Folder C/..../filename" I might want to know Folder A and Folder
B, and the filename.

For this, I would use the formula in part 1 above to determine position of
"/" and then in cell C1 would then put formula: =RIGHT(A1,LEN(A1)-FIND(<CR>,
A1)), to calculate the remaining path.
Then I would calculate the next folder and the right hand side of path after
that...and so on.

This will find the 4th item between /s (i.e., "Folder A" in your
example):

=MID(LEFT(A1, FIND("@", SUBSTITUTE(A1, "/", "@", 5)) - 1), FIND("@",
SUBSTITUTE(A1, "/", "@", 4)) + 1, 255)

This would find the name of the folder containing the folder containing
the file (e.g., 2 levels above the filename, or Folder C, in your
example):

=MID(LEFT(A1, FIND("@", SUBSTITUTE(A1, "/", "@", LEN(A1) -
LEN(SUBSTITUTE(A1, "/", "")) - 1)) - 1), FIND("@", SUBSTITUTE(A1, "/",
"@", LEN(A1) - LEN(SUBSTITUTE(A1, "/", "")) - 2)) + 1, 255)

 

[Craig Roberts]

First, thanks to JE McGimpsey for previous help and response to query. I've
re-posted this problem here:

This will find the 4th item between /s (i.e., "Folder A" in your
example):

=MID(LEFT(A1, FIND("@", SUBSTITUTE(A1, "/", "@", 5)) - 1), FIND("@",
SUBSTITUTE(A1, "/", "@", 4)) + 1, 255)

This would find the name of the folder containing the folder containing
the file (e.g., 2 levels above the filename, or Folder C, in your
example):

=MID(LEFT(A1, FIND("@", SUBSTITUTE(A1, "/", "@", LEN(A1) -
LEN(SUBSTITUTE(A1, "/", "")) - 1)) - 1), FIND("@", SUBSTITUTE(A1, "/",
"@", LEN(A1) - LEN(SUBSTITUTE(A1, "/", "")) - 2)) + 1, 255)

I have a manual solution that works now, which is similar to your suggestion
- but I don't substitute / with @ and back again - but I was really
wondering whether I should get cleverer and start using lists or some other
programmable feature in Excel, which I've not yet bothered with.

Any ideas?

Regards
C.