Formula text to columns

[billinr]

I have a few formulas resulting from a vlookup:
=IF(ISERROR(VLOOKUP(K17&" "&M17&" "&O17&"
"&Q17,J29:J73,1,FALSE)),"",VLOOKUP(K17&" "&M17&" "&O17&"
"&Q17,J29:J73,1,FALSE))

This returns the correct answer, but when I try to separate the result into
columns, all I can see is the formula in the text to columns work box.
Since I want to leave this formula in place to use for future changes, I
don't want to use a copy/ paste values and lose the formula.
Is there a way to work with the values and still keep the formula?

TIA

 

[Ron Rosenfeld]

I have a few formulas resulting from a vlookup:
=IF(ISERROR(VLOOKUP(K17&" "&M17&" "&O17&"
"&Q17,J29:J73,1,FALSE)),"",VLOOKUP(K17&" "&M17&" "&O17&"
"&Q17,J29:J73,1,FALSE))

This returns the correct answer, but when I try to separate the result into
columns, all I can see is the formula in the text to columns work box.
Since I want to leave this formula in place to use for future changes, I
don't want to use a copy/ paste values and lose the formula.
Is there a way to work with the values and still keep the formula?

TIA

Do the copy/paste values to a different column -- then work there.

Or, depending on the kind of result, you could use a formula to return the
portion of the answer that you want to different columns.
--ron

 

[Dave Peterson]

Maybe you can copy|paste special values into a different range?

 

[FSt1]

hi
you might may use of a helper column.
leave the below formula where it is (say a5) but in a blank column (say R5)
put the formula =A5. you can then copy and paste as value then to text to
columns in R5 keeping the original formula in A5 untouched.

Regards
FSt1

 

[Tyro]

If you copy the results in the cell containing the fomula then paste
special/values in another cell, you do not affect the formula at all. You
can then use text to columns to separate the pasted values

Tyro

 

[Gary''s Student]

Just do the work text-to-columns usually does for you. Say the lookup table
is:

1 hello world
2 happy day
3 smile often
4 help others
5 post frequently

with each phrase in a single cell. In A1, the formula:
=VLOOKUP(2,J1:K5,2)
returns:
happy day

So in B1 put:
=LEFT(A1,FIND(" ",A1,1)-1)
and in C1 put:
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))



In a way this is actually better than text-to-columns because it will
automatically parse the words as the VLOOKUP() changes

 

[billinr]

Thanks, Ron
That will give me the result I want, but I would have to run the copy/ paste
each time I use the sheet.
Can you suggest a macro or formula to make that happen automatically?

 

[Ron Rosenfeld]

Thanks, Ron
That will give me the result I want, but I would have to run the copy/ paste
each time I use the sheet.
Can you suggest a macro or formula to make that happen automatically?

You can try recording your copy/paste keystrokes to start off developing a
macro.

Or you can post more detail about your data setup, how it changes, and what
sort of results you require, and we can probably come up with something here.
--ron