Concatenate and Trim question

[bflan0524]

hello if column A row 1 says RM1-CT and Column B says Joe Smith how can i get column C Row 1 to say RM1-CT-Smith

 

[bflan0524]

there could be multiple underscores in the name column, for example Joe_Smith or Joe_Van_Smith etc

 

[Rick Rothstein]

there could be multiple underscores in the name column, for example Joe_Smith or Joe_Van_Smith etc

Yes, but could there also be Joe Smith or Joe Van Smith as well?

 

[bflan0524]

yes

 

[Rick Rothstein]

Yes, but could there also be Joe Smith or Joe Van Smith as well?

yes

Give this formula a try...

=G1&"-"&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(N1,"_"," ")," ",REPT(" ",99)),99))

 

[jtakw]

Ok,

This will cover both:

Book1
	A	B	D
1	RM1-CT	Joe Smith	RM1-CT-Smith
2	RM1-CT	Joe_Smith	RM1-CT-Smith
3	RM1-CT	Joe_ Smith	RM1-CT-Smith
4	RM1-CT	Joe _Smith	RM1-CT-Smith
5	RM1-CT	Joe _ Smith	RM1-CT-Smith
6	RM1-CT	Joe_ _Smith	RM1-CT-Smith
7	RM1-CT	Joe_Van_Smith	RM1-CT-Smith
8	RM1-CT	Joe Van Smith	RM1-CT-Smith
Sheet16

Cell Formulas
Range	Formula
D1	=A1&"-"&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(B1&" "," ","_"),"_",REPT(" ",50)),75))

 

[bflan0524]

this worked perfect..ty
could you please break it down for me? thank you for your time on this

 

[Rick Rothstein]

this worked perfect..ty
could you please break it down for me? thank you for your time on this

We cannot tell from your post who you are addressing your comment to.

 

[jtakw]

You're welcome.
Here are links to the functions used in the formulas:
https://support.office.com/en-us/article/right-rightb-functions-240267ee-9afa-4639-a02b-f19e1786cf2f
https://support.office.com/en-us/article/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332
https://support.office.com/en-us/article/rept-function-04c4d778-e712-43b4-9c15-d656582bb061

@Rick, probably to both, both formulas work.