SEARCH, FIND, LEFT...??

V

virfir97

x-no-archive: yes
Help required.

I have comma delimited strings of various lengths. I wish to extract
the first 3 fields as a comma delimited string. Since the fields are of
different lengths, I've hit a brick wall.

Example
107,165,72,9,30,10,4_________________ Result => 107,165,72
1,87,4,40,12,20,75,40,39,9,82,24_____ Result => 1,87,4
5____________________________________ Result =>5
120__________________________________ Result =>120
12,327_______________________________ Result =>12,327
mussels______________________________ Result =>mussels
2,263,106,82,19______________________ Result =>2,263,106

Can anyone help with a formula? Currently I use text to column to
achieve my aims, but I then have to concatenate after parsing. Since I
require the original data, I'm trying to write a formula that does
this automatically.

Thanks in Advance.
 
K

KL

Hi,

Assuming that the symbol "^" could never appear in your strings, try this:

=""&IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))<4,A1,LEFT(A1,SEARCH("^",SUBSTITUTE(A1,",","^",3))-1))

Regards,
KL
 
V

virfir97

x-no-archive: yes
Hi,

Assuming that the symbol "^" could never appear in your strings, try this:

=""&IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))<4,A1,LEFT(A1,SEARCH("^",SUBSTITUTE(A1,",","^",3))-1))

Regards,
KL
Click to expand...



Cheers KL it works.

I get the gist of some of your formula, but if it's not too much
trouble, do you mind explaining in English what every part of your
formula is doing.

Thanks in Advance.
 
K

KL

Hi,

First of all please change "<4" in the formula to "<3" :) (sorry for this
mistake)
=""&IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))<3,A1,LEFT(A1,SEARCH("^",SUBSTITUTE(A1,",","^",3))-1))
... explaining in English what every part of ... formula is doing.
Click to expand...

1) IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))<3,A1,...
checks how many occurences of "," (comma) are there in the evaluated string
and if less than 3, returns the whole string unchanged. The way it finds the
number of commas in the string is by comparing the lengths of the origional
string and the same one with the commas removed by the function SUBSTITUTE

2) LEFT(A1,SEARCH("^",SUBSTITUTE(A1,",","^",3))-1)
if the previous condition is not met and the number of commas in the string
is 4 or higher, then:

A= SUBSTITUTE(A1,",","^",3)
replaces all occurrences of the comma [starting from the 3rd occurrence] by
a symbol that is unlikely to be found in the string otherwise (in this case
I chose "^")

B= SEARCH("^",A)
returns the position of the first "^" in the string returned by the
previously discussed part

C= LEFT(A1,B-1)
returns the characters from the first to the number of the position of the
character preceding the first "^".

3) ""&...
(optional) Serves to return all the results as text strings as otherwise
single numerical values would be numbers as opposed to several comma
delimited.


Hope this helps,
KL
 
Top