How to get the first 3 letters from First and Last Name

[tobimac]

For example, Tire Rack, I would like to get the first 3 letters from First (Tire) and Last Name (Rack) as TIRRAC.

Please help me with any formula that can do this in excel.

 

[Blade Hunter]

=upper(left(a1,3) & mid(a1,find(" ",a1)+1,3))

 

[pjmorris]

Firstly, welcome to the forum.

Try this: (assuming your data is in cell E20): =UPPER(LEFT(E20,3)&MID(E20,FIND(" ",E20)+1,3))

Snap @Blade Hunter

 

[Blade Hunter]

Alternatively use this UDF:

Function FirstX(MyText As String, X As Long)
FirstX = UCase(Left(MyText, X) & Left(Split(MyText, " ")(UBound(Split(MyText, " "))), X))
End Function

Then in the sheet put =FirstX(A1,3) where 3 is the num chars to return from each word

Why would I do this? The formula I posted above will fail if you have something like "William J Smith" as it looks for the first space, this UDF takes the first and last word, regardless of spaces, this can also be done using a REPT(" ",255) function but this UDF was fun to build so I went that way

 

[Blade Hunter]

And this is how you do it with the REPT instead of the UDF:

=UPPER(LEFT(A1,3) & LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),3))

Here are some results to show what I mean:

Excel 2010
	A	B	C	D
1	Tire Rack	TIRRAC	TIRRAC	TIRRAC
2	William J Smith	WILJ S	WILSMI	WILSMI
3	Harold Hold	HARHOL	HARHOL	HARHOL
4	Edger J Hoover	EDGJ H	EDGHOO	EDGHOO
Sheet1

Cell Formulas
Range	Formula
B1	=UPPER(LEFT(A1,3) & MID(A1,FIND(" ",A1)+1,3))
C1	=UPPER(LEFT(A1,3) & LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),3))
D1	=FirstX(A1,3)

 

[tobimac]

Thanks. Works like a charm.

 

[tobimac]

Thanks. Works like a charm. This one works too.

 

[tobimac]

Thanks Peter. The formula works as expected.

 

[tobimac]

Thanks everyone. I was able to use the formulas you provided to get the value.

 

[GerryZ]

And this is how you do it with the REPT instead of the UDF:

=UPPER(LEFT(A1,3) & LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),3))

Here are some results to show what I mean:

Excel 2010
	A	B	C	D
1	Tire Rack	TIRRAC	TIRRAC	TIRRAC
2	William J Smith	WILJ S	WILSMI	WILSMI
3	Harold Hold	HARHOL	HARHOL	HARHOL
4	Edger J Hoover	EDGJ H	EDGHOO	EDGHOO
Sheet1

Cell Formulas
Range	Formula
B1	=UPPER(LEFT(A1,3) & MID(A1,FIND(" ",A1)+1,3))
C1	=UPPER(LEFT(A1,3) & LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),3))
D1	=FirstX(A1,3)

@Blade
I'm Learning
I have a question, what does it mean a Formula with REPT or with UDF?
Thank you for your teaching!