Remove only numeric strings from a value in the cell

[pgft]

Good morning everyone.

I'm using Excel 2010.

In a series of cells, all contained in a single column, I have some values that end with a numeric string. In practice, the value in the cell represent the name and surname of many employees, followed by an alphanumeric string that represents the employee ID number and consists of:

space
-
space
numeric string of variable length.

Some cells are deprived of the numeric string. The full name and surname of the employee is of variable length.

For example (these names are obviously fictitious):

Robert Callaghan - 2246
Alan Cornwall
Elizabeth McCormick Addams - 1522

I have to extract only the text string that is placed before the numeric characters and the character "-".
I tried with the division of the text into columns, but as the name is of variable length I can't determine where I have to place the delimiter.

Could anyone help me?

Thanks in advance.

[samba_ravi]

=LEFT(A1,SMALL(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&" - "&1/17),1)-4)
If you have your data in A1 Try the above
assume that there is always " - " before number

[Special-K]

=IF(ISERROR(FIND(" - ",A1)),A1,LEFT(A1,FIND(" - ",A1)-1))
and copy down the column

[Ali Kırksekiz]

Please Login or Register  to view this content.

[pgft]

=LEFT(A1,SMALL(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&" - "&1/17),1)-4)
If you have your data in A1 Try the above
assume that there is always " - " before number

Thanks for the reply.
Unfortunately, the formula does not work.

I have my data starting from cell B2, and the Italian version requires me to correct some points.
For example: "," shall be corrected to ";" and the names of the functions must be translated into Italian, but even if the translation is correct, the formula does not work yet.

Perhaps I did not explain it properly.
If this string exists in the cell: "Robert Callaghan - 2246", using the formula I want to get only "Robert Callaghan".

Using your formula that does not happen.

Surely I'm wrong. Could you explain better?

[pgft]

Thank you all for the answers.

Unfortunately, the formulas do not work. I explained in response to the user "nflsales" which is the problem.

I can't understand what is the error that I commit.

[Ali Kırksekiz]

I attached sample sheet

[pgft]

I attached sample sheet

Wonderful!

This is that I needed.

Excuse me, but at first I thought it was a code of VisualBasic

Thank you very much!

[Tony Valko]

Try this...

Data Range

	A	B
2	Robert Callaghan - 2246	Robert Callaghan
3	Alan Cornwall	Alan Cornwall
4	Elizabeth McCormick Addams - 1522	Elizabeth McCormick Addams

This formula entered in B2 and copied down:

=TRIM(LEFT(A2;FIND("-";A2&" -")-2))

You'll have to translate the function names if needed.

[pgft]

Try this...

Data Range

	A	B
2	Robert Callaghan - 2246	Robert Callaghan
3	Alan Cornwall	Alan Cornwall
4	Elizabeth McCormick Addams - 1522	Elizabeth McCormick Addams

This formula entered in B2 and copied down:

=TRIM(LEFT(A2;FIND("-";A2&" -")-2))

You'll have to translate the function names if needed.

Thanks for your reply.
I solved with the example of Ali Kirksekiz.

[AlKey]

English (US)

=SUBSTITUTE(LEFT(B1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&1/17))-1),"-","")

Italian

Translated formula

=SOSTITUISCI(SINISTRA(B1;MIN(RICERCA({0;1;2;3;4;5;6;7;8;9};B1&1/17))-1);"-";"")

Row\Col	B	C
1	Robert Callaghan - 2246	Robert Callaghan
2	Alan Cornwall	Alan Cornwall
3	Elizabeth McCormick Addams - 1522	Elizabeth McCormick Addams

[Tony Valko]

Good deal. Thanks for the feedback!