Extracting and replacing the first word in a String of text

[RAYCV]

Hi, I am trying to extract and remerge data with an additional character
(comma). For example, I have the following list in a column with many
variables

Jones Bob Mr
Campbell Jock Mr & Mrs
Smith Bob Banking Trust

and want it to be displayed as

Jones, Bob Mr
Campbell, Jock Mr & Mrs
Smith, Bob / Banking Trust


I have used =LEFT(G9,FIND(" ",G9)-1) to find the text and then
=H9&","&RIGHT(G9,FIND(" ",G9)*1) to concatenate the text, but it doesn't
return all the data for the 3rd record?? What am I doing wrong

Thanks in advance

 

[Sam Wilson]

If Jones Bob Mr is in A1:

=REPLACE(A1,FIND(" ",A1),1,", ")

 

[Ron Rosenfeld]

Hi, I am trying to extract and remerge data with an additional character
(comma). For example, I have the following list in a column with many
variables

Jones Bob Mr
Campbell Jock Mr & Mrs
Smith Bob Banking Trust

and want it to be displayed as

Jones, Bob Mr
Campbell, Jock Mr & Mrs
Smith, Bob / Banking Trust


I have used =LEFT(G9,FIND(" ",G9)-1) to find the text and then
=H9&","&RIGHT(G9,FIND(" ",G9)*1) to concatenate the text, but it doesn't
return all the data for the 3rd record?? What am I doing wrong

Thanks in advance

The "number of characters" argument for your right function is incorrect.

For this technique, you should use either:

=B1&","&RIGHT(A1,LEN(A1)-FIND(" ",A1)+1)

or (shorter):

=B1&","&MID(A1,FIND(" ",A1),99)

(where 99 is some number larger than the length of your longest string)

However, in your third example, you have inserted a "/" after the Bob. Are you
trying to do this with a formula? Or is it a typo?
--ron

 

[Vijay]

Put this formula in h9 and then drag it down
=LEFT(G9,FIND(" ",G9)-1)&", "&RIGHT(G9,LEN(G9)-FIND(" ",G9)*1)

vijay

 

[alanglloyd]

Hi, I am trying to extract and remerge data with an additional character
(comma). �For example, I have the following list in a column withmany
variables

Jones Bob Mr
Campbell Jock Mr & Mrs
Smith Bob Banking Trust

and want it to be displayed as

Jones, Bob Mr
Campbell, Jock Mr & Mrs
Smith, Bob / Banking Trust

I have used =LEFT(G9,FIND(" ",G9)-1) to find the text and then
=H9&","&RIGHT(G9,FIND(" ",G9)*1) to concatenate the text, but it doesn't
return all the data for the 3rd record?? �What am I doing wrong

As you're just inserting a comma, why not find the space and use that
position to extract both the pre-comma & post-comma text.

=MID(G9,1,FIND(" ",G9)-1)&","&MID(G9,FIND(" ",G9),50)

Similar technique for the stroke insertion.

Alan Lloyd

 

[Jacob Skaria]

Try
=SUBSTITUTE(A1," "," , ",1)

If this post helps click Yes