Clean up data-Convert to Numbers

pto160

Well-known Member
Joined
Feb 1, 2009
Messages
505
Office Version
  1. 365
Platform
  1. Windows
I am trying to clean up data for numbers that are formatted wrong. For example, the first number has [image]. The last [image] is the decimal point. The other [image] I don't need.
Some of the other numbers are formatted as text. Also I need the decimal point inserted in the number for example for the last two rows.

Book4
AB
1Column 1What I like
221[image]078[image]4721,078.47
3178[image]40178.40
4287.44287.44
5516516.00
6516.00516.00
7516[image]00516.00
828.5628.56
915865158.65
102468122,468.12
Sheet1
 
How can you tell when the decimals need to be inserted like row 9 and 10 as opposed to row 5?
 
Upvote 0
IF in fact the cell has [image], please try this
Book3
DEF
421[image]078[image]4721,078.47
Sheet1
Cell Formulas
RangeFormula
F4F4=SUBSTITUTE(D4,"[image]","")/100
 
Upvote 0
Changed, please try this
Book3
DEF
421[image]078[image]4721,078.47
5516516.00
Sheet1
Cell Formulas
RangeFormula
F4F4=IF(ISNUMBER(SEARCH("[image]",D4))>0,SUBSTITUTE(D4,"[image]","")/100,D4)
F5F5=IF(ISNUMBER(SEARCH("[image]",D5))*1>0,SUBSTITUTE(D5,"[image]","")/100,D5)
 
Upvote 0
Thanks Jeffrey Mahoney for your solution. Looking at my data again, the numbers without [image] are numbers formatted as text.

Cubist, that is a good point about row 5. Now that I am looking at my data, 516 in the cell is formatted as text 516.00. Disregard 516. I suppose multiply that *1. The [image] at the end is the decimal point. If it has already a decimal point, multiply by 1.
Disregard row 9 and 10. [Image] at the end is the decimal point.


Can I combine the two formulas where the numbers formatted as text to multiply by 1 and the rows with [image] in Jeffery Mahoney solution to put a decimal point?
 
Upvote 0
Here is the revised sheet.

Book4
AB
1Column 1What I like
221[image]078[image]4721,078.47
3178[image]40178.40
4287.44287.44
5516516.00
6516.00516.00
7516[image]00516.00
828.5628.56
Sheet1
 
Upvote 0
Try:
Book1
ABC
1Column 1What I likeCubist
221[image]078[image]4721078.4721078.47
3178[image]40178.4178.4
4287.44287.44287.44
5516516516
6516516516
7516[image]00516516
828.5628.5628.56
Sheet10
Cell Formulas
RangeFormula
C2:C8C2=IFERROR(--A2,SUBSTITUTE(A2,"[image]","")/100)
 
Upvote 0
Absolutely fantastic. Thank you so much. This works great. (y) :)
Thank you as well Jeffrey Mahoney.
 
Upvote 0

Forum statistics

Threads
1,226,889
Messages
6,193,527
Members
453,805
Latest member
Daniel OFlanagan

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