Locating the First Comma in a Text

[Guest]

What I am trying to do is find a formula, that will locate the first comma in
a text string, and then the next, until there is no more. Becasue I have
some text information, that is seprated by commas, and I now need to put this
information in different cells. BTW this information has been download from
the interent, so somebody is just not keying this stuff in.

 

[Don Guillett]

Merry Xmas
Have you tried
data>text to columns>pretty self explanatory

 

[Guest]

Yes I know about that and it works, but it is time consuming, when I have
more then one line to do. If I had formula, then it would happen
automaticlly for me.

 

[Don Guillett]

Just select ALL of them at once and then do it...

 

[Guest]

Guess I will have to see if I can find another source for the information
that I'm seeking. So far it is not forth coming here.

 

[T. Valko]

Text to Columns is the best solution.........

Using formulas can be somewhat complicated depending on how many commas may
be present. Does every entry have the same number of commas? Post several
representative samples.

Biff

 

[Guest]

The answer to your question is yes it does.

Example:
J. Brown, J. Simpson
O. Holmes, T. Johns
S. Oliver, K. Martin

 

[T. Valko]

Ok, that's relatively easy:

J. Brown, J. Simpson
O. Holmes, T. Johns
S. Oliver, K. Martin

Assume that data is in A1:A3

Enter this formula in B1:

=LEFT(A1,FIND(",",A1&",")-1)

Enter this formula in C1:

=IF(B1=A1,"",SUBSTITUTE(A1,B1&", ",""))

Select both B1 and C1 then copy down as needed.

Biff

 

[T. Valko]

Ok, that's relatively easy:

J. Brown, J. Simpson
O. Holmes, T. Johns
S. Oliver, K. Martin

Assume that data is in A1:A3

Enter this formula in B1:

=LEFT(A1,FIND(",",A1&",")-1)

Enter this formula in C1:

=IF(B1=A1,"",SUBSTITUTE(A1,B1&", ",""))

Select both B1 and C1 then copy down as needed.

Biff

 

[Guest]

Assuming your data in A1:A3

B1 =LEFT(A1,FIND(",",A1)-1)
C1 =RIGHT(A1,LEN(A1)-(FIND(",",A1)+1))
Select B1 & C1 and drag down to C3

 

[Guest]

Thanks to both of you for helping me on this problem. It works just perfect.
And my you both have a Merry Christmas.

Steve

 

[Guest]

Folks you don't how valuable that formula was to me. It as answer and solved
several forumla problems that I was having, in other aspect in my worksheet.
I have tried on several different worksheets, and it has worked everytime.

Thanks again so much.

Steve

 

[T. Valko]

You're welcome!

Biff

Folks you don't how valuable that formula was to me. It as answer and
solved
several forumla problems that I was having, in other aspect in my
worksheet.
I have tried on several different worksheets, and it has worked everytime.

Thanks again so much.

Steve