#Name? error when using Text to Columns

Question

I'm using the excel Text to Columns function.  The original value is https://www.abc.com/^+cd efg hijklmn

I want to separate the content with the delimiter ^. The expected outcome is https://www.abc.com/ and +cd efg hijklmn

And in the data preview window, it shows as expected.

While when I finish the Covert Text to Column Wizard Step3 of 3, it shows #Name? where it should be +cd efg hijklmn, and when I click, it shows =+cd efg hijklmn.

Can anyone help and explain why? I've never met this problem before. And this function always work well.

Thanks so much!

Answer 1

Hi, you can solve this problem by following simple ways like below.

• Assume that, Your text https://www.abc.com^+cd efg hijklmn is in A1 cell.
• Click A1 Cell to select it, then Click Data | Text to Column
• Select Delimited and Click Next.
• Select Other and Type ^ and Click Next.

Now here is the trick by which you can solve your problem.

• In Data Preview window Click on General column where +cd efg hijklmn shows. This will highlight that column as background color Black.
• Now Select the Text as Column Data Format.

• Click Finish and you are done.

Answer 2

Excel thinks you are creating a formula because it is starting with the +. Thus it adds = in front of it.

You could do a replace in that column, replacing = with nothing. That should fix it.

Answer 3

Thanks Faysal for your kind reply. It is really specific and clear!

I tried the Text format before but I didn't realize that it should be set to the +cd efg hijklmn column.

And your blog is great!:))

Answer 4

Thanks Jason so much for your quick and effective help! It works!