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

[diversification]

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?

 

[Logit]

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.