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
Harris_Lawrence	Harris, Lawrence

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=LEFT(A1,FIND("_",A1)-1) & ", " & MID(A1,FIND("_",A1)+1,1000000)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.