Hello, I have a question - in general, I don't know how to deal with it. I have an excel file with data at work (most likely dumped from some other system). In the column with amounts (thousands are as if manually separated by a space), however, excel does not really see this space. Using...

There are no spaces in this sheet, after all, for example in A1 there is the number 813.26 separated by a comma and everywhere. They are also right-aligned.

Hello, And can you see up to cell A5, A10, A11, A12 etc ... (thousands are separated by a space, and excel does not see it, you have to manually delete the space).

Hello! Select the unwanted character by jumping the cursor on the formula bar and copy it to the clipboard. Then, in the free cell, enter the formula: = CODE (V), where V - pressing CTRL + V (pasting the character). Excel will return the ASCII code of the character, 160 in this case. Then in the window, while holding down ALT, tap from the NUMERIC KEYPAD and not from the line above the letters 0160. Your character like a space will appear in the window, and replace it with nothing in the window. best regards Jan

In fact, it is as you write, my friend - the problem is that it is probably a spreadsheet imported from Office-like packages such as Open-Exel - I personally had such incompatibilities in MS Office in terms of cell format. The only thing you can do is to write a loop macro (for a given range of cells) that will eliminate this supposed error, but ... the sign replacement method given above by a colleague works on the spot.

Jan, thanks a lot. Unfortunately, my knowledge of Excel did not know this number. ps. where could some strange 'sign' have sneaked in from there? Is the system from which the data was dumped not compatible with Excel reading data?

Excel: Remove Unrecognized Spaces in Amounts Column for Accurate Data Processing

raker 42478 6

Treść została przetłumaczona

Zobacz oryginalną wersję tematu

Reply | New topic

#1 5093004 30 Apr 2008 21:18

raker raker

Level 17
» |

Post #1
5093004 30 Apr 2008 21:18

Hello,
I have a question - in general, I don't know how to deal with it. I have an excel file with data at work (most likely dumped from some other system). In the column with amounts (thousands are as if manually separated by a space), however, excel does not really see this space. Using ctrl + h (find: spaces, replace with: 'nothing') excel says there are no spaces in the selected data.
Does anyone know any other way to automatically bring this data back to working condition?

Attachments:

przykład.xls Download (15.5 KB)
ADVERTISEMENT
#2 5093055 30 Apr 2008 21:31

kranzio kranzio

Level 26

» | Helpful post? (0)

Post #2
5093055 30 Apr 2008 21:31

There are no spaces in this sheet, after all, for example in A1 there is the number 813.26 separated by a comma and everywhere. They are also right-aligned.
ADVERTISEMENT
#3 5093079 30 Apr 2008 21:36

raker raker

Level 17

» | Topic author Helpful post? (0)

Post #3
5093079 30 Apr 2008 21:36

Hello,
And can you see up to cell A5, A10, A11, A12 etc ... (thousands are separated by a space, and excel does not see it, you have to manually delete the space).
ADVERTISEMENT
#4 5093117 30 Apr 2008 21:44

spinnaker spinnaker

Level 32

» | Helpful post? (+1)

Post #4
5093117 30 Apr 2008 21:44

You mean "Use a separator".
#5 5093120 30 Apr 2008 21:44

jdjan1 jdjan1

Level 26

» | Positive voted Helpful post? (+16)

Post #5
5093120 30 Apr 2008 21:44

Hello!

Select the unwanted character by "jumping" the cursor on the formula bar and copy it to the clipboard.
Then, in the free cell, enter the formula:
= CODE ("V"), where V - pressing CTRL + V (pasting the character).
Excel will return the ASCII code of the character, 160 in this case.
Then in the window, while holding down ALT, tap from the NUMERIC KEYPAD and not from the line above the letters 0160. Your character "like a space" will appear in the window, and replace it with "nothing" in the window.

best regards

Jan
#6 5093149 30 Apr 2008 21:50

Anonymous Anonymous

Level 1

» |

Post #6
5093149 30 Apr 2008 21:50

In fact, it is as you write, my friend - the problem is that it is probably a spreadsheet imported from Office-like packages such as Open-Exel - I personally had such incompatibilities in MS Office in terms of cell format. The only thing you can do is to write a loop macro (for a given range of cells) that will eliminate this "supposed" error, but ... the sign replacement method given above by a colleague works "on the spot".
ADVERTISEMENT
#7 5093156 30 Apr 2008 21:51

raker raker

Level 17

» | Topic author Helpful post? (+1)

Post #7
5093156 30 Apr 2008 21:51

Jan, thanks a lot. Unfortunately, my knowledge of Excel did not know this number.
ps. where could some strange 'sign' have sneaked in from there? Is the system from which the data was dumped not compatible with Excel reading data?
Create an account, log in and become active in a forum and ads will not appear. You will receive points by participating in discussions.
Join this discussion.

Install the application

Didn't find an answer? Ask Artificial Intelligence

*I agree to send the question to OpenAI, Anthropic PBC, Perplexity AI, Inc., Kagi Inc., Google LLC - owners of language models in order to prepare the best response. The companies may monitor and log information entered into the form.

*I agree to publicly display my question and answer. The question and answer will be publicly available to everyone. The process may take a few minutes. Upon completion, you will be redirected to the page with the answer.

Wait...(2min)

Reply | New topic

Report a violation of the law

Topic summary

The discussion revolves around an Excel user facing issues with unrecognized spaces in an amounts column, likely due to data imported from another system. The user attempts to remove these spaces using the find and replace function but is unsuccessful. Responses suggest that the spaces may be non-breaking spaces (ASCII code 160) and recommend using a formula to identify and replace these characters. One user mentions the possibility of incompatibility with data from Office-like packages, suggesting a macro as a potential solution for bulk processing.
Summary generated by the language model.

Excel: Remove Unrecognized Spaces in Amounts Column for Accurate Data Processing

Didn't find an answer? Ask Artificial Intelligence

Topic summary