Get certain characters from text

[LindseyMonahue0]

Hi all,

I would like to ask for your help.

I have the following text in cell B2: 'John Smith' and I need to get only OHN starting from the left.

I've tried using the =LEFT function but it gets JOH but I need to get OHN.

Thank you excel pros.

 

[mumps]

Try: =MID(B2,2,3)

 

[LindseyMonahue0]

Hi mumps,

I appreciate the reply -- it worked. Now new problem, the names on the columns vary in length, do you have a possible solution in mind?

Btw, in COLUMN B, listed are the First and Last Names and I just need to get the last 3 letters of the First name. ( The data contains a lot of names and splitting it would be very tideous and also, considering that some people have 2 First Names. )

Thanks so much!

 

[mumps]

Can you list a representative list of names?

 

[LindseyMonahue0]

I'm sorry what do you mean by list a representative list of names?

I may be wrong but for an example here are just few of the names:

Andrea Jones
Brent Anderson
Cornelius Waters
David Weiss
Edward John Smith
John Smith
Karen Mae Owens
Yu Xiu Ping

I just need to get the last 3 letters of each of the First Names. I've been thinking over it for hours, lol.

Btw, thank you so much mumps.

 

[bosco_yip]

Assume your data put in A1:A8

In B1, copied down :

=RIGHT(LEFT(A1,-LOOKUP(1,-FIND(" ",A1,ROW($1:$99)))-1),3)

Regards
Bosco

 

[MickG]

Try this UDF:-

Function Nam(nStr [COLOR="Navy"]As[/COLOR] String)
[COLOR="Navy"]If[/COLOR] Len(Split(nStr, " ")(0)) < 3 [COLOR="Navy"]Then[/COLOR]
    Nam = Split(nStr, " ")(0)
[COLOR="Navy"]Else[/COLOR]
    Nam = Right(Split(nStr, " ")(0), 3)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] Function

To Save and Run UDF:-
Copy Function from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.


In sheet, Add the "function" in a cell "B1" as :- =Nam(A1)
Click Return.
Drag UDF down for results for other Names

Regards Mick

 

[LindseyMonahue0]

Hi,

I've first tried Bosco's way and it worked like what I expected.

Thanks so much!

I'll also try rhat Mick, I'll let you know how it goes.

I appreciate everyone on this thread.

Love,
Lindsey