Data, Text to Columns, has commas in the text, plus as a delimeter
in [General]
|
Prev: Autosum doesn't work. How do I get column total?
Next: while opening excel attachment getting repair error
From: Tonso on 20 May 2010 08:33 Using XL 2003, I want to import data from another program. I can do so with the Text to Columns feature, but, the program uses exclamation points and a comma as a delimeter ( !,! ). An example is below: !Visit Arizona, Eat Mexican Food!,!1!,!1.000!,!N!,!50!,!50!,!0!,! 2229!,!547!,!188!,!0!,!0!,!0!,!0!,!0!,!0!,!N! As you can see, commas are sometimes used in the text, plus as a delimeter. How can I handle this? Thanks, Tonso
From: Dave Peterson on 20 May 2010 08:49 I would try bringing the data into a single column. Then select that column and Edit|Replace what: !,! with: ! replace all You may want to try doing the same thing before you import the data--open the text file in NotePad (or your favorite text editor and do the equivalent edit|replace). ps. If the ! shows up in the text, then I'd change it to a unique/unused character (maybe the vertical bar |). Tonso wrote: > > Using XL 2003, I want to import data from another program. I can do so > with the Text to Columns feature, but, the program uses exclamation > points and a comma as a delimeter ( !,! ). An example is below: > !Visit Arizona, Eat Mexican Food!,!1!,!1.000!,!N!,!50!,!50!,!0!,! > 2229!,!547!,!188!,!0!,!0!,!0!,!0!,!0!,!0!,!N! > > As you can see, commas are sometimes used in the text, plus as a > delimeter. How can I handle this? > > Thanks, > > Tonso -- Dave Peterson
From: Tonso on 20 May 2010 09:21
On May 20, 8:49 am, Dave Peterson <peter...(a)verizonXSPAM.net> wrote: > I would try bringing the data into a single column. > > Then select that column and > Edit|Replace > what: !,! > with: ! > replace all > > You may want to try doing the same thing before you import the data--open the > text file in NotePad (or your favorite text editor and do the equivalent > edit|replace). > > ps. If the ! shows up in the text, then I'd change it to a unique/unused > character (maybe the vertical bar |). > > Tonso wrote: > > > Using XL 2003, I want to import data from another program. I can do so > > with the Text to Columns feature, but, the program uses exclamation > > points and a comma as a delimeter ( !,! ). An example is below: > > !Visit Arizona, Eat Mexican Food!,!1!,!1.000!,!N!,!50!,!50!,!0!,! > > 2229!,!547!,!188!,!0!,!0!,!0!,!0!,!0!,!0!,!N! > > > As you can see, commas are sometimes used in the text, plus as a > > delimeter. How can I handle this? > > > Thanks, > > > Tonso > > -- > > Dave Peterson Thanks Dave! This works great!! Tonso |