Formula to pickup Digits from a text sentanc and Add Zero at last if digit

**tariqnaz2005** · 01-13-2010, 09:21 AM

Dear Expirts,

As i Always learn from you guys a lot of...

Again ..here i m stuck..i have 20000 address data bank..

I have 3 questions..

1. can i add Zero at the last of sentance i.e..Block 2, street 12, house 123, i want to add "00" in front of "Block 2" cuz it has only one digit at last, as well as "0" infront of Street cuz it has 2 digits at last,

2. Can i pick up digit from a sentance like.."Block2, street-12, house 576" i want only "2 and 12 and 576"

3. some of my cells have extra spaces before "Street 2", how can i remove extra spaces from begining and last so that each cell would be arranged so that i can count length..or put formula..

As attached sheet, all is messup and not arranged will..

Is there an idea that how can i pick up "2" or "120" from "block 2, street 120" etc.

And also if in cell A1 i have " street 2 " (total length is 8) but there is also 2 or 3 space before Street and after 2, so total length or characters are 12 instead of 8 in cell A1, how can arrange them only 8 by a formual..

Also to sort a column i need to add "0" or "00" or "000" in front of Block 12, street145, house 4, so that it would be as Block 002, sreet 013, house 145,

is there a formula so if "House 2" has only 1 digit at last and "street 12" has 2 digits at last..i need a formla which can automatically read the number of digits at last and add Zero "0" or "00" or "000" as required..

Pls give me a fvour..

Thanks in advance..

Test address.xls

I

**davesexcel** · 01-13-2010, 08:20 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**tariqnaz2005** · 01-14-2010, 12:45 AM

THANKS FOR REPLY,

I attached a sheet, which showing my requirements n problems..

pls help me..i want a formula that if in column A has " bayan block 345, shetedjldjdsljsdljdkdf " than column B1 give me result only 345..thanks

**contaminated** · 01-14-2010, 01:41 AM

To extract numbers use this formulae

=VALUE(MID(B3,MATCH(1,ISNUMBER(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)*1)*1,0),SUM(ISNUMBER(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)*1)*1)))

Confirm with <Ctrl>+<Shift>+<Enter> and copy down.

To remove spaces use TRIM function eg

=VALUE(RIGHT(TRIM(B10),3))

Or abovestated

Can u also explain. Do you want the output to be 002, 012 or block2strettbokck, block -45, byan area should be block002strettbokck, block -045, byan area

**tariqnaz2005** · 01-14-2010, 02:21 AM

Thanks a lot of..

I really appreciate it..it works nicely. same as per my requirements..

God bless you and give you the returns..thanks..

**DonkeyOte** · 01-14-2010, 02:35 AM

Another alternative re: number extraction:

C3:
=LOOKUP(9.99E+307,--MID(B3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B3&1234567890)),ROW(A$1:INDEX(A:A,LEN(B3)))))
copied down

the above is not an Array so can be confirmed with Enter as normal.

**tariqnaz2005** · 01-17-2010, 07:44 AM

To remove spaces use TRIM function eg

Code:
=VALUE(RIGHT(TRIM(B10),3))

Thanks for every one...Now i m able to separate numbers from text..but how can i separate following sentense..."London - block 124"...

I want london in another column and 124 in other clomun..but there are some speces in between like " - "..i dont want these extra speces for both text and numbers even in begining or end...

thanks..