How to delete the $ in a cell from a csv file

English
, , ,
#1

Hello. Am little new at this open source software. I received a CSV sales file showing product codes and $sales delimited by commas. Except the person that created the file actually appended a $ to the front of the sales number in each field. This makes the number a text field when I import it into calc because it looks like $411.01 for example

I tried “replace all” with $ to remove and nothing to add… but Librecalc then inserts a ’ in place of the $ which changes nothing.

How do I get rid of the $ and cause the text field to become a number or currency field?

Many thanks for your advice.

#2

Hi: Use Regular Expressions in FIND&REPLACE and mask the Dollar character this way: \$.

If there is the ' as leading figure of a cell that means text format. You may eliminate it by searching .* (Regular Expressions) and replace it with & or $0.

In some cases (for entire columns) use Data menu | Text to Columns… as easiest proceeding.

Godspeed

#3

Wow. a third good dea. I tried it out to see what I could learn and it works. Now I have to say I don’t understand why this works.

I never would have thought of $. What does the \ do?

And I have no idea what a regular expression is but it works when I check the box.

#4

@eastavin Regular Expressions:

I never would have thought of $. What does the \ do?

.

You can use the $ figure as a “joker” representing some kind of expression. For that reason it is sort of built-in operator. Its meaning can be end of paragraph or - using brackets in FIND - REPLACE of same content what was found in brackets.

When using FIND&REPLACE and RegEx you must mask it if only regular $ figure is meant. For masking the backslash is used.

#5

If you still have access to the original CSV file: in the import dialog choose the English-US locale and enable Detect special numbers. The imported field then will be numeric currency formatted.

#6

Or, if you still have access to the original CSV file, simply use a text editor to replace the $-signs. For example, use Notepad++ (notepad-plus-plus.org), use Edit → Replace, specify the $-sign as the target character and make sure the replace-with window is empty (no space), then click on Replace All. Done.

#7

Thanks. I overlooked the obvious one. I tried your suggestion afterward and it worked too.

#8

Which of course removes the “this is a Dollar currency amount” connotation and leaves a bare number. But YMMV…

#9

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.