formula needed to convert text numbers to numbers with automatic deletion of empty rows

[RAM72]

Hi All

Need all possible formula that can be used to convert text numbers to numbers with automatic deletion of empty rows between data. The import data is cpmpleted in text format however i tried= (value cell) no sucess , tried paste special multiply by one no sucess.

Can someone help me thanks

 

[Luke M]

I'm not 100% sure on what your numbers are. If this "208,40" means twenty thousand, eight hundred forty, then use this formula copied down:
=IF(ROWS(A$2:A2)>COUNTA(A:A),"",VALUE(SUBSTITUTE(INDEX(A:A,2*ROWS(A$2:A2)),",","")))

If it means two hundred eight and forty cents, then use:
=IF(ROWS(A$2:A2)>COUNTA(A:A),"",VALUE(SUBSTITUTE(INDEX(A:A,2*ROWS(A$2:A2)),",",".")))

Both formula will automatically remove gaps in your data, and can be copied down as far as you would think would be necessary.

 

[RAM72]

I'm not 100% sure on what your numbers are. If this "208,40" means twenty thousand, eight hundred forty, then use this formula copied down:
=IF(ROWS(A$2:A2)>COUNTA(A:A),"",VALUE(SUBSTITUTE(INDEX(A:A,2*ROWS(A$2:A2)),",","")))

If it means two hundred eight and forty cents, then use:
=IF(ROWS(A$2:A2)>COUNTA(A:A),"",VALUE(SUBSTITUTE(INDEX(A:A,2*ROWS(A$2:A2)),",",".")))

Both formula will automatically remove gaps in your data, and can be copied down as far as you would think would be necessary.

Hi Luke
it is the second formula that I used it worked well thank you but it did not deleted the empty rows could you adjust the formula.

I'm not 100% sure on what your numbers are. If this "208,40" means twenty thousand, eight hundred forty, then use this formula copied down:
=IF(ROWS(A$2:A2)>COUNTA(A:A),"",VALUE(SUBSTITUTE(INDEX(A:A,2*ROWS(A$2:A2)),",","")))

If it means two hundred eight and forty cents, then use:
=IF(ROWS(A$2:A2)>COUNTA(A:A),"",VALUE(SUBSTITUTE(INDEX(A:A,2*ROWS(A$2:A2)),",",".")))

Both formula will automatically remove gaps in your data, and can be copied down as far as you would think would be necessary.

Hi Luke

Used 2 nd formula it works well thank you but however does not delete the empty the empty rows .

Could you please adjust formula
Regards
RAM72

 

[Luke M]

Not sure what you mean...in your screen cap, there are no blank cells in col B. Note that formulas can't add/delete rows, they can only pass along information.

If you truly need the rows deleted, rather than using a formula, follow this process:

1. Select Col A
2. Hit Ctrl+g to bring up Go To dialogue
3. Click on Special, select Blank cells, hit ok
4. Hit Ctrl + minus symbol. On Delete dialogue, select entire rows, hit ok.
5. Select col A
6. Do a find and replace. Replace "," with "."

Done. Blank rows have been deleted, data has been converted to numbers.

 

[XLPadawan]

Hi Luke
it is the second formula that I used it worked well thank you but it did not deleted the empty rows could you adjust the formula.


Hi Luke

Used 2 nd formula it works well thank you but however does not delete the empty the empty rows .

Could you please adjust formula
Regards
RAM72

View attachment 8657

RAM72, see if this is what you need: