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

 

[Dryver14]

=a1& "-" & right(b1,len(b1)-find("*",substitute(b1," ","*",len(b1)-len(substitute(b1," ","")))))

 

[jtakw]

Hi,

Here's another way:

Book1
	A	B	C
1	RM1-CT	Joe Smith	RM1-CT-Smith
Sheet16

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

 

[bflan0524]

Hi,

Here's another way:

	A	B	C
1	RM1-CT	Joe Smith	RM1-CT-Smith

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

Sheet16​

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

<tbody>
</tbody>

hello,
i get a #VALUE error when i do this one

 

[jtakw]

I can't replicate your Error, you may have hidden characters in those cells, how are the values in A1 and B1 populated?

 

[bflan0524]

so i had actually put columns A& B in my example for simplicity stake, in my workbook they are column G and N. here is the formula i put in, maybe i made a typo that i am not seeing. does this formula look right

=G2&"-"&MID(N2,FIND(" ",N2)+1,255)

 

[jtakw]

That formula looks fine and should work, it's a pretty simple formula.
Are the values in G2 and N2 manually entered, as a result of some formula, or imported from somewhere?

 

[bflan0524]

actually i see the issue in column N the name i entered as follows for example Joe_Smith
how would i account for the _ between the names
lastly could you also break down for the me what the formula means/does so i fully understand it as opposed to just copying a formula

thanks for your time and help

 

[Rick Rothstein]

actually i see the issue in column N the name i entered as follows for example Joe_Smith

Is the separator between the first and last name always going to be an underbar character or will it sometimes be an underbar character and other times a space character between the first and last names?

 

[jtakw]

actually i see the issue in column N the name i entered as follows for example Joe_Smith
how would i account for the _ between the names
lastly could you also break down for the me what the formula means/does so i fully understand it as opposed to just copying a formula

thanks for your time and help

You never said anything about the "_" underscore, and why explain a formula that isn't working for you, let's get it working first, try this, it'll cover SPACE, UNDERSCORE, SPACE UNDERSCORE, UNDERSCORE SPACE, SPACE UNDERSCORE SPACE, UNDERSCORE SPACE UNDERSCORE:

Book1
	A	B	C
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
Sheet16

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

If you don't need all those possibilities, let us know, we can make the formula simpler.