Re: SEARCH, FIND, LEFT...??

[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

<virf...@yahoo.com> wrote in message
news:1125053199....@o13g2000cwo.googlegroups.com...
> 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.
>

[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.

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