formula question

[bflan0524]

hello
in column A i have a name that appears as Lawrence_Harris
in Column B i would like it to appear as Harris, Lawrence

any thoughts on how i could do this

ty

 

[jproffer]

If they all have the underscore, this could work for you:

Excel 2013/2016

	A	B
1	Harris_Lawrence	Harris, Lawrence

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =LEFT(A1,FIND("_",A1)-1) & ", " & MID(A1,FIND("_",A1)+1,1000000) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Of course that's assuming you don't have someone with a last name over 1,000,000 characters .

 

[jtakw]

Hi,

This is one way, but I'm almost certain that your Single sample may not be a true representation of all your data:

Book1
	A	B
1	Lawrence_Harris	Harris, Lawrence
Sheet31

Cell Formulas
Range	Formula
B1	=MID(A1,FIND("_",A1)+1,255)&", "&LEFT(A1,FIND("_",A1)-1)

 

[jproffer]

Oops S#!+......use jtakw's formula. I had mine backwards.

 

[jtakw]

Hi jproffer, I think you have your A1 data backwards...

Edit: was posting this when you were posting the above

If OP's data is like you posted, then a single SUBSTITUTE function could do.

 

[bflan0524]

thank you guys !!

 

[jtakw]

You're welcome.