Extract String from cell

[sharky12345]

I'm trying to find a way I can extract the string from cell values.

Each cell will have a unique number which will always begin with 'N' followed by a series of numbers, which can be any number though not likely to be more than 6. After that there is a surname, followed by first name(s) and then a gender.

Here are a couple of examples;

N342 STORNCH ANNE FEMALE

or

N76 STEVENS JOHN GEORGE UNKNOWN

If it's possible, and I appreciate it may be a big ask, I need the strings extracted so that I end up with 3 columns showing 'N' number, name and then gender.

Can anyone assist?

 

[Peter_SSs]

Could you give a few (say 5) more examples of the actual cells values and the expected results?
It isn't clear what you want extracted.

 

[etaf]

assuming text in A2

in B2 put
=LEFT(A2,FIND(" ",A2,1)-1)

in C2 put
=MID(A2,FIND(" ",A2,1)+1,LEN(A2)-LEN(RIGHT(A2,LEN(A2)-SEARCH("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))-FIND(" ",A2,1)-1)

in D2 put
=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", LEN(TRIM(A2)))), LEN(TRIM(A2))))

 

[hiker95]

sharky12345,

Here is a macro solution for you to consider based on your two displayed strings.

I also assume that your strings begin in cell A2.

Sample raw data, and, results in the active worksheet:

Excel 2007
	A	B	C	D	E	F
1
2	N342 STORNCH ANNE FEMALE	N342	STORNCH	ANNE	FEMALE
3	N76 STEVENS JOHN GEORGE UNKNOWN	N76	STEVENS	JOHN GEORGE	UNKNOWN
4
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub sharky12345()
' hiker95, 10/01/2016, ME967992
Dim c As Range, s, n As Long
Application.ScreenUpdating = False
With ActiveSheet
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    s = Split(c, " ")
    n = UBound(s)
    Select Case n
      Case 2
        c.Offset(, 1) = s(0)
        c.Offset(, 2) = s(1)
        c.Offset(, 3) = s(2)
      Case 3
        c.Offset(, 1) = s(0)
        c.Offset(, 2) = s(1)
        c.Offset(, 3) = s(2)
        c.Offset(, 4) = s(3)
      Case 4
        c.Offset(, 1) = s(0)
        c.Offset(, 2) = s(1)
        c.Offset(, 3) = s(2) & " " & s(3)
        c.Offset(, 4) = s(4)
    End Select
  Next c
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the sharky12345 macro.