ExcelGuys - extracting a number from a text string (1 Viewer)

[Ted Lange as your Bartender]

Anyone know a formula I could use in Excel that could extract the numbers from just the left side of a text string that always contains a hyphen in the middle?

For example:

72021969-101 Wells

How do I pull just the left sided number into a separate cell and leave out the “101” and everything else?  Note that the number of digits on the left could vary in size so a simple “=LEFT” function wouldn’t consistetly work.  I also found a more complex function on the inter webs involving LEN and SUBSTITUTE functions but it doesn’t seem to want to cut out digits to the right of the dash.

TIA

 

[Jaysus]

Is the data static?  Does it need to be a formula?  Would delimited text-to-columns work?

 

[BroadwayG]

If you've got a hypen as delimiter:

=LEFT(A1, FIND("-",A1)-1)

 

[[scooter]]

Anyone know a formula I could use in Excel that could extract the numbers from just the left side of a text string that always contains a hyphen in the middle?

For example:

72021969-101 Wells

How do I pull just the left sided number into a separate cell and leave out the “101” and everything else?  Note that the number of digits on the left could vary in size so a simple “=LEFT” function wouldn’t consistetly work.  I also found a more complex function on the inter webs involving LEN and SUBSTITUTE functions but it doesn’t seem to want to cut out digits to the right of the dash.

TIA

My cheater method is to use Text-To-Columns and then use the minus sign as the delimiter.

 

[[scooter]]

Feels like the answer is going to be in here somewhere:

https://exceljet.net/excel-functions/excel-left-function

Maybe Split Text String At Specific Character

 

[rodg12]

If you've got a hypen as delimiter:

=LEFT(A1, FIND("-",A1)-1)

This...if all you want at the end is the numbers left of the hyphen.

 

[MindCrime]

If you've got a hypen as delimiter:

=LEFT(A1, FIND("-",A1)-1)

This guy knows things.

 

[Ted Lange as your Bartender]

If you've got a hypen as delimiter:

=LEFT(A1, FIND("-",A1)-1)

Perfect - thanks

 

[bryhamm]

Perfect - thanks

Keep in mind that the formula given will return a text result.  If you need the number as a value, you can add =VALUE(orig formula) as well.