Is there a way to accomplish what Text-To-Columns does using a function?

diversification

New Member
Joined
Jun 24, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Nevermind.

So a lot of times when someone imports data and tries to use INDEX MATCH, VLOOKUPS, etc, they won't get any matches. A lot of the time, selecting the data and doing a Text-To-Columns on it will resolve this (unchecking all delimiters and so on.) It doesn't actually change the way the data looks, but it must manipulate something about the formatting. My belief was that it either changes text to number values, or vice versa, but if that's the case, using TEXT or VALUE functions should make the two pieces of data match, but they don't.

Can anyone explain what the heck the Text To Columns function is actually changing, and whether there's a way to do this using a formula?
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,219
Well ... your post conversation is crossed out ... so ... if you are still seeking an answer :

Record a macro while you are going through the steps of performing a Text To Columns manual conversion. Then review the macro.
 

Forum statistics

Threads
1,144,370
Messages
5,723,960
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top