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

tobimac

New Member
Joined
Jan 28, 2015
Messages
6
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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 :)
 
Upvote 0
Alternatively use this UDF:

Code:
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 :)
 
Last edited:
Upvote 0
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
ABCD
1Tire RackTIRRACTIRRACTIRRAC
2William J SmithWILJ SWILSMIWILSMI
3Harold HoldHARHOLHARHOLHARHOL
4Edger J HooverEDGJ HEDGHOOEDGHOO
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0
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
ABCD
1Tire RackTIRRACTIRRACTIRRAC
2William J SmithWILJ SWILSMIWILSMI
3Harold HoldHARHOLHARHOLHARHOL
4Edger J HooverEDGJ HEDGHOOEDGHOO
Sheet1
Cell Formulas
RangeFormula
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!
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top