Combining text strings

H

Hayley

i am trying to use concatenate to i have a cell:
Woods T 150/1

i need it to look like
Woods T, 150/1

can anyone tell me how i do this
Thanks
 
M

Max

Assuming you have
in A1: Woods T
in B1:150/1

Put in C1: =TRIM(A1&", "&B1)
C1 returns: Woods T, 150/1

Copy C1 down
 
M

Max

Slight tweak, for more consistency in output,
Put instead in C1: =TRIM(A1)&", "&TRIM(B1)
 
H

Hayley

Thankks Max,

But the whole string is in A1

A1 = Woods T 150/1
I need B1 to read Woods T, 150/1
 
H

Harlan Grove

Hayley wrote...
....
But the whole string is in A1

A1 = Woods T 150/1
I need B1 to read Woods T, 150/1
Click to expand...
....

If the comma should be just before the second space, try

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

If the comma should be just before the last space, try

=REPLACE(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"
&LEN(A1))),1)=" "),ROW(INDIRECT("1:"&LEN(A1)))),0,",")

If the comma should be before the space to the left of the first
numeral, try the *array* *formula*

=REPLACE(A1,MATCH(TRUE,ISNUMBER(-MID(A1,
ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1,0,",")
 
C

CLR

My choice would be to do Data > TextToColumns with a space delimiter to blow
the string apart into three columns.......then use this formula to
CONCATENATE it back together,

=A1&" "&B1&", "&C1

then Copy > PasteSpecial > Values......

Vaya con Dios,
Chuck, CABGx3
 
H

Hayley

Thank you that worked great never used this before and you've just saved me
hours of work.

Thanks Again
Hayley
 
Top