Extract first number in a string, and the three letters after the first number

**TRICKYT57** · 07-17-2021, 05:17 PM

I have some strings such as "28,456.0324 ADA-9F4", which contain all start with a number followed by some characters and maybe additional numbers. I would like to extract the first number, and also extract the first three letters which follow the first number. So in the string mentioned above the outpuit of the two formulas would be "28'456.0324" and "ADA".

I already searched around, and found an extremely long formula to extract a number, but that didn't work as it was designed to turn all numbers in the string into one big number, and effectively ignored the decimal point.

An example spreadsheet is attached with the desired outcome shown in yellow.

**Pete_UK** · 07-17-2021, 05:26 PM

I assume the apostrophe ( ' ) towards the end of your second sentence is a mis-type.

Pete

**TRICKYT57** · 07-17-2021, 05:55 PM

Originally Posted by Pete_UK

I assume the apostrophe ( ' ) towards the end of your second sentence is a mis-type.

Pete

Actually not a mistype. Here in Europe, especially in finance, we use an apostrophe as the thousands separator, rather than a comma. Excel number formatting is set up that way for us by default. However, lets ignore the formatting which I can do. I just want the number to come out as 28456.0324.

The reason we use an apostrophe instead of the old-fashioned Anglo-Saxon comma that we learned in school has to do with the French. In France, the comma is used in place of the full stop in numbers. So whereas the Anglo Saxons would write 123.456 the French would write 123,456. It looks like a completely different number. So to avoid confusion in Europe, the apostrophe is commonly used as the thousands separator instead of a comma, especially in Excel.

Of course the brits are no longer in the EU, so I suppose the comma will prevail there.

**MarvinP** · 07-17-2021, 06:38 PM

Hi,

See the attached with two formulas you want.

Formula:

Please Login or Register  to view this content.

extract first number and the 3 letters follwoing first number for Tricky.xlsx

**Ghozi Alkatiri** · 07-17-2021, 07:12 PM

at B3
=MID(A3,FIND(" ",A3)+1,3)
at C3
=LEFT(A3,FIND(" ",A3))
copy down

Extract first number in a string, and the three letters after the first number

LinkBack

Thread Tools

Rate This Thread

Display

Extract first number in a string, and the three letters after the first number

Re: Extract first number in a string, and the three letters after the first number

Re: Extract first number in a string, and the three letters after the first number

Re: Extract first number in a string, and the three letters after the first number

Re: Extract first number in a string, and the three letters after the first number

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] extract number from string..with letters.

[SOLVED] Removing varied number of letters from end of number-letter string

Convert a string of letters to a number

[SOLVED] Extract letters before first number

[SOLVED] How to extract Fist number, Last number from string and SUM

Converting string of letters to Number

Extract number from text/number string..

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions