extracting value from an errant formula

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,017
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>
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Audioa84

Board Regular
Joined
Oct 28, 2013
Messages
61
Try putting the code below into a vba module. Then reference the function in the cell but typing =returnVal(Row()).
Code:
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.
 
Last edited:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,877
Hi

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

Audioa84

Board Regular
Joined
Oct 28, 2013
Messages
61

ADVERTISEMENT

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

Board Regular
Joined
Mar 11, 2010
Messages
136
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

Well-known Member
Joined
Oct 24, 2007
Messages
1,017
-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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,877
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top