Using 'Text to columns' when value to split is derived from a formula

[Aysha]

Hi All,

First time on here so apologies if this thread may have been answered in the past.

I am trying to use 'Text to Columns' to split out data with the delimiter _.

ie. 111_AL_TEST_01_01

However the above is actually the value of a formula, so when I go to use the 'Text to Columns' it's only showing the formula and not the value.

Does anyone have any ideas on how to force 'Text to Columns' to use the value and not the formula?

Thank you in advance!!
aysh

[Special-K]

Copy the cells and Paste Values back to the same cells. They're now values.
Now use Text to Columns.

If you don't want to paste to the same cells paste them to a new column and use Text to Columns on that.

[sktneer]

Hi Aysha,
Welcome to the Forum.

You may also try this formula to split the string obtained with a formula into columns.
Assuming that your formula string is in A1, then try this...

	A	B	C	D	E	F	G	H
1	111_AL_TEST_01_01	111	AL	TEST	01	01
2
3

In B1

Please Login or Register  to view this content.

and then copy across (right) the row until you get blank cells.