convert text to columns

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I want to be able to run a text to column function from a column within a
Pivot table, so that when the Pivot table is refreshed, the results of the
text to column function automatically update. Not sure if this is actually
possible. Any help would be appreciated. Many thanks
 
Hi Paul,

I don't know of a way to do this in a pivot table, and it would be a great
feature to add if it doesn't exist.

The following example is how to use worksheet functions to parse data from a
cell.

If A2 has the following text:
first column text;second column text; third column text

In B2:
=LEFT(A2,FIND(";",A2,1)-1)
will display as:
first column text

In C2:
=MID(A2,FIND(";",A2,1)+1,LEN(A2)-FIND("~",SUBSTITUTE(A2,";","~",2),1))
will display as:
second column text

In D2:
=RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,";","~",2)))
will display as:
third column text

To find the 2nd instance of a semicolon ";" - the substitute function
replaces a character and allows specification of which instance. In this
example the tilde was used as a replacement character - however the
replacement character can be any character except a wild character such as an
asterisk * , and the replacement character must be unqiue within the cell.
 
Back
Top