Separating text columns

[jolculbe]

One of our systems spit out a report of first and last names. Instead of spitting out the report into 2 separate columns, it combined the first and last names into one column and separated the names with a dash (-) (i.e. "John-Smith" as one cell).

Typically I would use text-to-columns, but since the length of the names vary so much (1100+ total line items), I'm not sure that's possible. Is there a formula that can be used that searches for the '-' within the cell and tells it to split it at that point?

Any help would be greatly appreciated!

Thanks!

 

[pagrender]

for the first name:
=LEFT(A1,SEARCH("-",A1,1)-1)

for the last name:
=RIGHT(A1,LEN(A1)-SEARCH("-",A1,1))

 

[ljungren13]

<TABLE style="WIDTH: 471pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=628 border=0><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 1974" width=108><COL style="WIDTH: 135pt; mso-width-source: userset; mso-width-alt: 3291" width=180><COL style="WIDTH: 255pt; mso-width-source: userset; mso-width-alt: 6217" width=340><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=108 height=20>John-Smith

</TD><TD class=xl64 id=td_post_2367795 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 135pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=180>=LEFT(A1,SEARCH("-",A1)-1)</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 255pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=340>=MID(A1,SEARCH("-",A1)+1,LEN(A1)-SEARCH("-",A1))</TD></TR></TBODY></TABLE>

Name was in A1

 

[jolculbe]

Thanks for the help! It is appreciated and will save me a great deal of time!

 

[SaladProblems]

Just curious, why does the length of the name matter? It sounds like you're trying to do text to columns using a fixed width, but at least in 2007 you can choose a character with which to delimit it.

I believe the same option exists in 2003 but I'm not sure. What version are you using?

 

[ljungren13]

your welcome and yes, you can use text to columns and delimited data and use the character other and type "-" and it should work just fine