Text to columns - First Instance of =

**hyyfte** · 03-06-2007, 11:03 AM

Is there any way to use Text to columns feature and only use the first equals as a delimiter?
For example we have a spreadsheet of translations:
Column A
german = Deutsch
italian = Italiano
dropdown64 = B = Zweisprachig
etc.

There are thousands of rows in this spreadsheet and many of them have the equals sign in the translation.

We would like to split this out into two columns:
Column A
german
italian
dropdown64

Column B
Deutsch
Italiano
B = Zweisprachig

Please help.
Thanks
Hyyfte

**NBVC** · 03-06-2007, 11:11 AM

I don't think you easily can...

you can try a couple of formulas though.

in B1: =LEFT(A1,FIND("=",A1)-2) copied down

in C1: =SUBSTITUTE(A1,B1& " = ","") copied down

Then copy both columns, B and C, select B1 and paste special as Values.

You can then delete Column A.

**hyyfte** · 03-06-2007, 11:33 AM

just tried this and it worked well for the majority of the fields. Where I'm having trouble is when the equals sign doesn't have a space before or after.
e.g.
german= Deutsch
italian = Italiano
dropdown64 =B = Zweisprachig
is there a way to use an OR statement with a substitute?

**NBVC** · 03-06-2007, 11:39 AM

Try these formulas instead:

=LEFT(A1,FIND("=",TRIM(SUBSTITUTE(A1,"="," = ")))-2)

and

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"="," = ")),B1& " = ","")

adjusting cell reference to suit.

**hyyfte** · 03-06-2007, 12:46 PM

This takes care of the first "=" but adds spaces when there are multiple "="s

adjustrpt23 = {0} Für Privatzwecke {1} Berechnet zu {2}/Einheit ({3})

splits to

B:adjustrpt23
C:{0} Für Privatzwecke {1} Berechnet zu {2}/Einheit ({3})

with spaces before and after the "=" after class.

How do we apply this to only the first equals?