Clean up data-Convert to Numbers

[pto160]

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
	A	B
1	Column 1	What I like
2	21[image]078[image]47	21,078.47
3	178[image]40	178.40
4	287.44	287.44
5	516	516.00
6	516.00	516.00
7	516[image]00	516.00
8	28.56	28.56
9	15865	158.65
10	246812	2,468.12
Sheet1

 

[Cubist]

How can you tell when the decimals need to be inserted like row 9 and 10 as opposed to row 5?

 

[Jeffrey Mahoney]

IF in fact the cell has [image], please try this

Book3
	D	E	F
4	21[image]078[image]47		21,078.47
Sheet1

Cell Formulas
Range	Formula
F4	F4	=SUBSTITUTE(D4,"[image]","")/100

 

[Jeffrey Mahoney]

Changed, please try this

Book3
	D	E	F
4	21[image]078[image]47		21,078.47
5	516		516.00
Sheet1

Cell Formulas
Range	Formula
F4	F4	=IF(ISNUMBER(SEARCH("[image]",D4))>0,SUBSTITUTE(D4,"[image]","")/100,D4)
F5	F5	=IF(ISNUMBER(SEARCH("[image]",D5))*1>0,SUBSTITUTE(D5,"[image]","")/100,D5)

 

[pto160]

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?

 

[pto160]

Here is the revised sheet.

Book4
	A	B
1	Column 1	What I like
2	21[image]078[image]47	21,078.47
3	178[image]40	178.40
4	287.44	287.44
5	516	516.00
6	516.00	516.00
7	516[image]00	516.00
8	28.56	28.56
Sheet1

 

[Cubist]

Try:

Book1
	A	B	C
1	Column 1	What I like	Cubist
2	21[image]078[image]47	21078.47	21078.47
3	178[image]40	178.4	178.4
4	287.44	287.44	287.44
5	516	516	516
6	516	516	516
7	516[image]00	516	516
8	28.56	28.56	28.56
Sheet10

Cell Formulas
Range	Formula
C2:C8	C2	=IFERROR(--A2,SUBSTITUTE(A2,"[image]","")/100)

 

[pto160]

Absolutely fantastic. Thank you so much. This works great.
Thank you as well Jeffrey Mahoney.