Finding numbers in text string

[Henry c]

Hi,

In a data report i have a column which contains a mix of text and may contain an 8 digit number which could start in 0.

for example

in column A i could have "Hello how are you 01237232 I am fine"

I wish to extract the number into a seperate column, and would ordinarilly use a mid or left/ right function, however the text infront and behind the number will vary in length, which means i cannot do this.

The number will always be 8 digits, could start in 0 but will not always, and it may not be present in all the cells in this column.

Has anyone got any pointers as to how I an achieve this?

Kind Regards

[daddylonglegs]

Is it the only number in the string? For a string in A1 this formula will give you the 8 characters starting with the first digit in A1

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),8)

[zbor]

With slightly improved daddy's approach you could find first number and extract it no matter how it's long:

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

(OK, "no matter how long" is figurative )

[Dulanic]

Depending on the number of characters... this is an alternative but this is an array formula and would need to be entered with CTRL SHIFT + ENTER. Should go up to 100 #s.

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$100),1)),0),COUNT(1*MID(A1,ROW($1:$100),1)))

[Henry c]

Daddy long legs That works brilliantly there will occasionally be other numbers, but these will not be 8 digits, is it possible to show an error or blank for these?

An example is "2), Nimr" or "19), Col" which the formular has returned,

Zbor, the number will always be 8 digits I think I can get away with the more simple one, but thanks

Many Thanks

[zbor]

Zbor, the number will always be 8 digits I think I can get away with the more simple one, but thanks

Well, you also said: there will occasionally be other numbers, but these will not be 8 digits

[Henry c]

Well, you also said: there will occasionally be other numbers, but these will not be 8 digits

Yes there will, but it is only the 8 Digit one I wish to extract.

Aplologies if this was taken the wrong way, I very much appreciate your advice.

[daddylonglegs]

This version will only find an eight digit number

=TEXT(LOOKUP(10^8,MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-7)),8)+0),"00000000")

it returns #N/A if there isn't such a number. You could make that a blank by changing to this

=TEXT(-LOOKUP(1,CHOOSE({1,2},0,LOOKUP(1,-MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-7)),8)+0))),"00000000;;")

[Henry c]

Thanks so much, great help

[Kate1]

Daddy long legs That works brilliantly there will occasionally be other numbers, but these will not be 8 digits, is it possible to show an error or blank for these?

An example is "2), Nimr" or "19), Col" which the formular has returned,

Zbor, the number will always be 8 digits I think I can get away with the more simple one, but thanks

Many Thanks

It happend to me too and then I figured out the formula was counting ALL the digists in the string, meaning, when there are more numbers further down, and it was "midding" the whole accumulated digits length which resulted in "so many" irrelevant additional characters. check your list and you will see.
Still this formula is genious!

[Alfredim]

Hello, I'm new in here, my name is Alfredo and I'm a studant from portugal. I have a work to do that is analyse a meteorological code on execel and divide it is colums.
I can't find any help on something I need to do. I need to extract every section of text from a string of text and numbers. Here is my problem: METAR LPMT 300900Z 35005KT 300V360 9999 FEW018 12/08 Q1026 (TWR:30.30) RMK HR76 CLD5 FEW080 BLU=
This is a meteorological code. I want to extract the 300V360 that is always formed by 3numbers,"V",3numbers; but it can be or not in the code, but can apear other "V" in the code, but not "sorounded" by numbers. I can't use the MID function because it can be in diferent places. I think I said it all. Any one can help me?
Thank you, and sorry for my bad english

[zbor]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.