extracting value from an errant formula

[Jeffrey Green]

I have a column of 700,000 Dealer Codes. each is 5 alpha-numeric characters long, if that helps.

Most were just numbers but my Vlookup didn't work because of formatting. So i, right or wrong, copied a 1 and paste-specialed multiply that column, thinking in it would only "fix" the number fields. I was wrong.

i have thousands that return #value error.
The formula in those cells shows =("8402A")*1

I need that 5 digit code back! in this case, 8402A.

How do i do that?
Thanks

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Audioa84]

Try putting the code below into a vba module. Then reference the function in the cell but typing =returnVal(Row()).

Function returnVal(lngRow As Long)   
   returnVal = Mid(Cells(lngRow, 1).Formula, 4, 5)
End Function

Also replace the 1 in Cells(lngRow,1) with the number of the column that has your data.

 

[Jonmo1]

try pressing
CTRL+Z (the UNDO function)

 

[pgc01]

Hi

-Delete the "*1", using replace
- copy->paste special values

 

[Audioa84]

pgc01's solution is much easier than mine. Just be sure to replace ")*1" with ")". If you just replace *1, it will overwrite your data (since you used the * wildcard).

 

[stefankemp]

Hi

-Delete the "*1", using replace
- copy->paste special values

The asterisk will be interpreted as a wildcard by find and replace, so if you try to do that it will delete everything in front of a "1" in every cell.

 

[Jeffrey Green]

-Delete the "*1", using replace
- copy->paste special values

I don't understand that . . i tried paste special with null, etc . . . nothing works.

Please explain. thanks

 

[pgc01]

Hi Jeffrey

Sorry, I just tried and this is how it worked.

I did 2 replacements

Select the cells

1st replacement

replace

=("

with nothing. This deletes the first part of the formula

2nd replacement.
With the cells still selected, replace

")~*1

with nothing. This deletes the end part of the formula

For ex.:

=("8402A")*1

becomes

8402A

Does this solve your problem?