• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract number from text/special character

M

Manasa

New Member
Hi,

I have dirty data of various length but maximum=18 characters. There are alphabets,-’s number combinations.
Eg:

21980760-XS2678
BB123830
9009
BB-10-24-900
lc1319038

I need the first 8 numbers from the text. I do not need numbers that appear second time after the alphabets. If there are not 8 (eg: 9009/lc1319038) I need to append 0’s in the front to make it 8 characters in length.

I used this formula

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

However it does not work for the BB-10-24-900 type text format. Please help !
 
Hi ,

Just to add to Shrivallabha's formula , so that it can be copied downwards :

=TEXT(LEFT(INT(NPV(-0.9,,IFERROR(MID(LEFT(A2,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",1))),1000))),1000-COLUMN($2:$2),1)%,""))),8),REPT("0",8))

entered as an array formula , using CTRL SHIFT ENTER.

The $65:$90 refers to upper-case A through upper-case Z.

The 1000 refers to the upper limit on the number of characters in the cell ; out of the 1000 , 992 can be alpha , and 8 can be digits.

The 8 refers to the number of digits that will be extracted ; if the number of digits in the string is more than 8 , the excess digits will be lost. Lesser number of digits will be left padded with zeros.

Narayan
 
Thank you Narayan. I was sure that I had forgotten something but was half asleep.

I used 19 as OP has informed that he will be having maximum of 18 characters.
 
Hi guys,

I just realized I have even full text and blanks and NA entries like eg:

Number not present
NNA
BLANK

I need the formula to retain these kinda full texts/blanks instead of converting them to 8 digit zeros.

Sorry I'm still a beginner in excel.

Thanks,
M
 
Hi ,

Are you likely to have 8 digit zeroes which you will want to be shown in the output ? If not , you can just have an IF statement , where if the output is 8 zeros , the input is shown as the output.

Narayan
 
You must log in or register to reply here.
Back
Top