SEARCH

The How-To Geek Forums Have Migrated to Discourse



Join the Discussion at discuss.howtogeek.com

How-To Geek Forums / Microsoft Office

(Solved) - Text to columns

(8 posts)
  • Started 6 months ago by WHITEHOUSEP
  • Latest reply from moreeg
  • Topic Viewed 481 times

  • View Accepted Answer
  • Remove Solved Status
WHITEHOUSEP
Posts: 12

I have a spreadsheet of data that was sent to me in Excel 97-2003.

One column of address data contains multiple fields that I need to split into separate columns. The data in each cell that I need to separate is separated by a paragraph / return which shows as a question mark in a box symbol. When using "Text to columns" I can't get excel to recognize these marks or a return.
This is an example of some data in one cell, the question mark in the box does not show when I copy and paste.

'28 MURIAL STREET

BARRHEAD

GLASGOW

If I convert text to column using either space or tab, it only converts the first line of text. Ideally I need three columns with the data from each line in each.

I hope this makes sense and that you can help!

WHP

Posted 6 months ago #
Top
 
moreeg
moreeg
Posts: 842

You can try to replace the check mark in a box with another unique character e.g. "~" or "[" (without quotes). Then do the data to columns using the character you chose.

If the check mark doesn't work then normally the new line in a cell is represented by the ascii 10 symbol. In the find box press ALT and then 0010 in the number pad. You'll need to close then reopen the file between each attempt.

Posted 6 months ago #
Top
 
WHITEHOUSEP
Posts: 12

Many thanks for your help. By using Find and Replace and the ascii 10 symbol, it has inserted commas where I need them however the check marks in a box character is still there. When I try to convert text to columns, this mark seems to stop the data transferring to a new column. Only the first line before the check mark in box character goes to a new column.

I tried to use Find and Replace to delete this mark but it says it cannot find any data to replace.

Any ideas?

WHP

Posted 6 months ago #
Top
 
moreeg
moreeg
Posts: 842

Hi WHITEHOUSEP

I ran into the same problem. Try saving and closing the file. Re-open the file and then try the find and replace again.

Posted 6 months ago #
Top
 
WHITEHOUSEP
Posts: 12

Thanks so much for your help. I have tried saving, closing and reopening the file but I can't replace these marks. I'll have to see if I can obtain the file in another format.

I appreciate your time, thx again!

Posted 6 months ago #
Top
 
moreeg
moreeg
Posts: 842

Hi WHITEHOUSEP

Another thing you can try is the "Clean" function which removes non-printable characters as in =CLEAN(A1) where A1 is where your address is. If that works you should copy all the "cleaned" cells and paste them as values. You can then convert them to columns.

Accepted Answer · Posted 6 months ago #
Top
 
WHITEHOUSEP
Posts: 12

That's it! Thx so much, worked like a dream and I've been able to convert text to column for whole spreadsheet.

Posted 6 months ago #
Top
 
moreeg
moreeg
Posts: 842

No problems - always happy to help.

Posted 6 months ago #
Top
 



Topic Closed

This topic has been closed to new replies. Please create a new topic instead.


Create New Topic Return to Forum