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]

A UDF is a User Defined Formula. This is a block of code which is fired when the function is entered.

Copy the code then right click on the tab and choose "View Code"

Paste my code then the function is available to you, you can then use =FirstX(CellReference,X) where CellReference is a Cell like A1 and X is the number of characters you want from each word.

The REPT one is pretty cool, I am a fan of this. It uses REPT (which is a Repeat function) to replace every space with 255 spaces, we do this so we can easily chop it up into single words and loads of spaces.

The spaces are then trimmed out back to just the word.

Example:

Substitute(A1," ",REPT(" ",255)) will be the value of A1 with all spaces now padded out to 255 spaces.

We can then say we want the right 255 characters of this very long string which we know will be a whole load of spaces and the word we want

We then use trim to remove all the spaces, now we just have the word we want

Then we take the left 3 cells of this word.

You could also use the Mid(Find function like we have above and then use a formula to count the number of spaces and use it to find that instance. There are loads of ways you can do this but personally I like the UDF the best, it's the cleanest option in my opinion.

 

[GerryZ]

@blade
Everythings absolutly clear
you are better than my school teacher
thank very much

 

[Rick Rothstein]

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

)

A little simpler (eliminates the double Split function call)...

Function FirstX(S As String, X As Long)
  FirstX = UCase(Left(S, X) & Mid(S, InStrRev(S, " ") + 1, X))
End Function