How to extract numbers from these cells

[jasonleewkd]

Hi experts, I'd like to extract numbers from these cells. I've searched online but i don't find any guide that tells me how to extract numbers when they are separated by a word. Appreciate any help. Thank you


1600 to 1700

400 to 500

900 to 1000

1500 to 1600

1500 to 1600

1300 to 1400

1200 to 1300

1400 to 1500

900 to 1000

900 to 1000

2100 to 2200

1000 to 1100

 

[sdsurzh]

Hi,


Can you please refer the below link.


http://www.excelbanter.com/showthread.php?t=241282


Thanks,

Suresh Kumar S

 

[Debraj]

Hi jasonleewkd,


can you please confirm .. the output..


* is it

A1 = 1600 to 1700

B1 = 1600

C1 = 1700


* or you want

A1 = 1600 to 1700

B1 = 1600

B2 = 1601

B3 = 1603

...

B1700 = 1700


I guess, I am going in wrong direction..


Regards,

Deb

 

[Hui]

Jasonleewkd


Do you want to convert the data above to numbers or extract or summarize data from another table according to these ranges?


Can you please be more specific or post a sample file

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[jasonleewkd]

Thanks Suresh, how forgetful that there is delimiter that I could use! However, I would like to learn how to get


A1 = 1600 to 1700

B1 = 1600

C1 = 1700


using a formula too please if possible

 

[Hui]

B1: =LEFT(A1,FIND(" ",A1))

C1: =RIGHT(A1,LEN(A1)-FIND("to ",A1)-2)

 

[shrivallabha]

C1 can be also written as:

=MID(A1,FIND("to ",A1,1)+3,10)