Finding a text string from within a text & numbers string

[mrfahaji]

Hi, I have searched for something like this as I'm sure I won't be the first to ask but I am only able to find examples of formulae that don't work in my situation!

I want to extract a name from within a text string, the string will look something like this:

5Jones
9Smith
15Johnson
22Williams

And I just want the names, i.e. "Jones", "Smith" etc. Because the numbers at the start of the string are different lengths I need a function which finds the first 'letter' character.

In an ideal world I'd also like the function to still pull out the name if the string looks like this:

5JonesEngland 45
9SmithCanada 67
15JohnsonUSA 73
22WilliamsCanada 80

Though I imagine that may be difficult - if those countries were the only three possible, can you integrate a FIND function with a Namelist? If not then I can probably split over a couple of columns and do multiple IF statements.

Apologies for splitting my question into two - I'd be happy if someone can help me with the basic one, and ecstatic if someone can solve the trickier one!

 

[Peter_SSs]

If a Country is involved and it is always followed by a number like your samples, then you could try the user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function GetName(s As String) As String
  Static RX As Object
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "[A-Z][A-za-z\-\']+[a-z](?=[A-Z]|$)"
  GetName = RX.Execute(s)(0)
End Function

Excel Workbook
	A	B
1	5Jones	Jones
2	9Smith	Smith
3	15Johnson	Johnson
4	22Williams	Williams
5	5JonesEngland 45	Jones
6	9SmithCanada 67	Smith
7	15JohnsonUSA 73	Johnson
8	22WilliamsCanada 80	Williams
9	23Smith-JonesCanada 67	Smith-Jones
10	18D'Olivera	D'Olivera
11	23MacPhersonUsa 73	MacPherson
Sheet1

 

[Special-K99]

Bit closer to the required result.
This extracts the number and name

=LEFT(A1,SUM(IFERROR(SEARCH({"England","Canada","USA"},A1),0))-1)

 

[Peter_SSs]

This extracts the number and name

.. unless you had an example like:
23CusackCanada 67

"England", for example, could also be a valid surname so the following would also trick your formula
23EnglandUSA 73

 

[mrfahaji]

Bit closer to the required result.
This extracts the number and name

=LEFT(A1,SUM(IFERROR(SEARCH({"England","Canada","USA"},A1),0))-1)

Thanks so much for replying (and also to Peter). I can't use VBA so this is closer to what I'm looking for. However, and I apologise for this - the countries do not always appear afterwards.

So the table that Peter has shown above is probably closer to what I might be aiming for.

Is there a formula which solves my basic problem first? If I can do that then I might be able to figure out a way around the countries bit!

 

[Rick Rothstein]

Is there a formula which solves my basic problem first? If I can do that then I might be able to figure out a way around the countries bit!

Will you ever have names with hyphens (eg., Smith-Jones) or apostrophes (eg., D'Olivera) or double sir name (eg., MacPhreson or DiDinato)?

 

[mrfahaji]

Will you ever have names with hyphens (eg., Smith-Jones) or apostrophes (eg., D'Olivera) or double sir name (eg., MacPhreson or DiDinato)?

Yes it's possible, unfortunately! The first part of the string should always be the same though, i.e a number (1 or 2 digits) followed by a name. The extra stuff (e.g. countries) is the only variable bit.

 

[Peter_SSs]

1. Do you have a defined list of possible countries? If so, where?

2. If a country appears after the name, is the country always followed by a space and then another number, like your samples so far?

 

[mrfahaji]

1. Do you have a defined list of possible countries? If so, where?

2. If a country appears after the name, is the country always followed by a space and then another number, like your samples so far?

Hi Peter - at the moment it's more theoretical, but I don't think there will be more than four countries and these will not change over time. The main problem for me at the moment is how to get rid of the numbers at the start of the string!

 

[Peter_SSs]

The main problem for me at the moment is how to get rid of the numbers at the start of the string!

If that is all you want to do, try this.

Excel Workbook
	A	B
1	5Jones	Jones
2	9Smith	Smith
3	15Johnson	Johnson
4	22Williams	Williams
5	5JonesEngland 45	JonesEngland 45
6	9SmithCanada 67	SmithCanada 67
7	15JohnsonUSA 73	JohnsonUSA 73
8	22WilliamsCanada 80	WilliamsCanada 80
9	23Smith-JonesCanada 67	Smith-JonesCanada 67
10	18D'Olivera	D'Olivera
11	23MacPhersonUsa 73	MacPhersonUsa 73
12	23CusackCanada 67	CusackCanada 67
13	23EnglandUSA 73	EnglandUSA 73
14	23EnglandEngland 22	EnglandEngland 22
Sheet2

However, if you do want to extract the name and the answer to both my previous questions is "Yes", then you could try this.

Excel Workbook
	A	B	C	D
1	5Jones	Jones		England
2	9Smith	Smith		Canada
3	15Johnson	Johnson		USA
4	22Williams	Williams
5	5JonesEngland 45	Jones
6	9SmithCanada 67	Smith
7	15JohnsonUSA 73	Johnson
8	22WilliamsCanada 80	Williams
9	23Smith-JonesCanada 67	Smith-Jones
10	18D'Olivera	D'Olivera
11	23MacPhersonUsa 73	MacPherson
12	23CusackCanada 67	Cusack
13	23EnglandUSA 73	England
14	23EnglandEngland 22	England
Sheet1