How to separate number and text from a string

[hardik.chevron]

Hi,
Let say I have few strings like
Houston 134.23.234.43
Austin 123.45.345.341
LasVegas 234.34.345.12
Port Arthur 123.23.232.1
St. louis 234.23.324.12

I want to separate city name and IP address in separte column. How can I do that? Which function should I use?

thanks,
Hardik

 

[Armando Montes]

Try text to columns.

 

[schielrn]

Welcome to the Board!

Can we always assume we want to seperate them at the last space?

 

[VoG]

Hi. Try this - note that it fails on St. Louis unless you change it to St Louis

Excel Workbook
	A	B	C
1	Houston 134.23.234.43	Houston	134.23.234.43
2	Austin 123.45.345.341	Austin	123.45.345.341
3	LasVegas 234.34.345.12	LasVegas	234.34.345.12
4	Port Arthur 123.23.232.1	Port Arthur	123.23.232.1
5	St. louis 234.23.324.12	#VALUE!	#VALUE!
Sheet4

 

[schielrn]

Based on my assumption above, try this:

Excel Workbook
	A	B	C
14	Houston 134.23.234.43	Houston	134.23.234.43
15	Austin 123.45.345.341	Austin	123.45.345.341
16	LasVegas 234.34.345.12	LasVegas	234.34.345.12
17	Port Arthur 123.23.232.1	Port Arthur	123.23.232.1
18	St. louis 234.23.324.12	St. louis	234.23.324.12
Sheet16

Cell Formulas
Range	Formula
B14	=LEFT(A14,FIND("~",SUBSTITUTE(A14," ","~",LEN(A14)-LEN(SUBSTITUTE(A14," ",""))))-1)
C14	=SUBSTITUTE(A14,B14&" ","")

Hope that helps.

 

[Haseeb Avarakkan]

Try;

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Houston 134.23.234.43</td><td style=";">Houston</td><td style=";">134.23.234.43</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Austin 123.45.345.341</td><td style=";">Austin</td><td style=";">123.45.345.341</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">LasVegas 234.34.345.12</td><td style=";">LasVegas</td><td style=";">234.34.345.12</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Port Arthur 123.23.232.1</td><td style=";">Port Arthur</td><td style=";">123.23.232.1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">St. louis 234.23.324.12</td><td style=";">St. louis</td><td style=";">234.23.324.12</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=TRIM(<font color="Blue">SUBSTITUTE(<font color="Red">A1,C1,""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">A1," ",REPT(<font color="Purple">" ",LEN(<font color="Teal">A1</font>)</font>)</font>),LEN(<font color="Green">A1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy down...

 

[hardik.chevron]

Thank you all for your great help!

Regards,
Hardik