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

[tariqnaz2005]

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]

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]

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]

To extract numbers use this formulae

Please Login or Register  to view this content.

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

To remove spaces use TRIM function eg

Please Login or Register  to view this content.

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]

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]

Another alternative re: number extraction:

Please Login or Register  to view this content.

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

[tariqnaz2005]

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..