Can't format these numbers

[tigerzen]

This was downloaded from a file at work. I've used CLEAN and a range of techniques to try and convert from text to numbers but nothing has worked. I'd like to format these cells in Currency format.

Support staff model.xlsm
	M
8	58,566
9	59,729
10	60,982
11	61,649
12	62,877
Sheet1

 

[RoryA]

If you select one of them, does the comma appear in the value in the formula bar? If so, they are definitely text. What does =LEN(M8) return?

 

[Sergius]

Hello!
Is that what you wanted?

58,57 €
59,73 €
60,98 €
61,65 €
62,88 €

If so, select the required cells and change their format to Currency. To do this, on the Home tab, in the Number block, click the arrow in the lower right corner. In the window that opens, select Currency from the list, and in the right part, the number of digits after the decimal point and the symbol of the required currency.

 

[tigerzen]

If you select one of them, does the comma appear in the value in the formula bar? If so, they are definitely text. What does =LEN(M8) return?

Hi Rory, the comma does appear in the formula bar, LEN formula returns 8, it looks like there are 2 spaces at the end.

 

[tigerzen]

Hello!
Is that what you wanted?

58,57 €
59,73 €
60,98 €
61,65 €
62,88 €

If so, select the required cells and change their format to Currency. To do this, on the Home tab, in the Number block, click the arrow in the lower right corner. In the window that opens, select Currency from the list, and in the right part, the number of digits after the decimal point and the symbol of the required currency.

Hi Sergius, that does not work on my spreadsheet.

 

[Dave Patton]

T202409a.xlsm
	M	N
8	58,566	$58,566.00
9	59,729	$59,729.00
5c

Cell Formulas
Range	Formula
N8:N9	N8	=--CONCAT(TEXTSPLIT(M8,{" ",","}))

 

[RoryA]

Select all the data, press Ctrl+H to bring up the Replace dialog, click in the Find box and hold the Alt key while typing 0160 on the number keys, then press Replace All.

 

[tigerzen]

T202409a.xlsm
	M	N
8	58,566	$58,566.00
9	59,729	$59,729.00
5c

Cell Formulas
Range	Formula
N8:N9	N8	=--CONCAT(TEXTSPLIT(M8,{" ",","}))

Hi Dave, that works! But how did you know to use that particular formula? I tried everything from Text to Columns to all sorts of Substitute formulae and nothing worked.
Does anyone know why this happens in a spreadsheet, can't remember this happening to me before.

 

[tigerzen]

Select all the data, press Ctrl+H to bring up the Replace dialog, click in the Find box and hold the Alt key while typing 0160 on the number keys, then press Replace All.

Hi Rory, my computer doesn't have a keypad

 

[Dave Patton]

quote "Hi Dave, that works! But how did you know to use that particular formula? I tried everything from Text to Columns to all sorts of Substitute formulae and nothing worked.
Does anyone know why this happens in a spreadsheet, can't remember this happening to me before."

We needed to remove the comma and the trailing blanks. One way to do that was as shown.
The double negative coerces the text to a number.
The cells were formatted "currency".