- Is thereany formula to seperate text and numbers contained in one cell as in "text to column" ???

Question

For example... In an excel sheet a cell contains an address "Flat no-303 Pkt-D Mayur vihar-1 ph-2 Tel [phone]", now I want the phone nos. to be placed seperately in a column next to the excisting cell.

Answer 1

You can do this in Excel, but it will take a few steps.

Providing formatting is just as presented in your question, here's how to do it:

1) Select the column.

2a) Replace [ Tel] with [ ;Tel] using Ctrl+H or Edit > Replace, depending on your version of Excel.
2b) If you want the two phone numbers in separate columns, also replace [ / ] with [ ;/ ].

Note: If you have semicolons (;) anywhere in the text for that column, use any other unique character instead.

3) Use Text to Columns selecting "Delimited" and "Semicolon" to insert breaks at the semicolons. (If you used a different character, select "Other" and type the character in the box instead of selecting "Semicolon.")

4) If necessary, replace [/ ] with nothing in the third column to get rid of the slash.

You should now have three columns with the address and telephone numbers in separate columns.

Answer 2

No sorry. You'll have to do it manually. Words and numbers are seen to be one piece of data in that cell.