Formula text to columns

  • Thread starter Thread starter billinr
  • Start date Start date
B

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
 
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
Click to expand...

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
 
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
 
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
 
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
 
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?
 
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?
Click to expand...

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
 
Back
Top