"text to columns" with numbers

[alfordtp]

I have cells that contain multiple values as a full string:

Apple - Banana - 000123

When I convert to three columns using the "text to columns" operation, the third column converts to a number and removes the leading zeros (cell value turns into 123 as a number). This is breaking my lookup function. Any ideas how to keep everything as text and exactly as it is shown?

TIA

Tim

 

[Tetra201]

In the Text-to-Columns Wizard, select the Text format for your third column.

 

[jtakw]

Hi,

Try this:

Text to Columns, Delimited, Next
Check "Other", Hyphen, Next
Select/Highlight 3rd column in preview pane (where the number is), Select "Text" upper left, Finish.

Edit, already answered, type too slow

 

[alfordtp]

Thank you for the full explanation. I didnt realize that you had to click each column and select text. I was clicking the text option, but apparently it was only for the first column.

 

[jtakw]

You're welcome, glad to help, thanks for the feedback.