need suggestions

[theHydra]

I have a bunch of Managers (50+)with names in the wrong format, I need to get from:

[TABLE="width: 227"]
<tbody>[TR]
[TD="class: xl63, width: 227"]Jones, Andy (L7F000915)
to
ABC1AJones (ABC1= site code)

not sure the best way to achieve, open to ideas.

Thank you![/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Formula suggestion..
B1 formula with "Site Code" hard-coded in formula.
C1 formula using Cell reference for "Site Code"

Book1
	A	B	C	D	E
1	Jones, Andy (L7F000915)	ABC1AJones	ABC1AJones		Site Code
2					ABC1
Sheet567

Cell Formulas
Range	Formula
B1	="ABC1"&MID(A1,FIND(",",A1)+2,1)&LEFT(A1,FIND(",",A1)-1)
C1	=E2&MID(A1,FIND(",",A1)+2,1)&LEFT(A1,FIND(",",A1)-1)

 

[theHydra]

Thank you so much! Worked perfectly.

 

[jtakw]

You're welcome, welcome to the forum.