I have a table with 3 columns and 300 rows of numbers (currency) with the letter B denoting billions after each number ($345 B). How can I delete the letter B from each cell, leaving the number unchanged, without editing 900 cells separately?
300 rows of numbers (currency)
Really numbers? - or are you in fact talking about text looking like and interpreted as numbers? If they are really numbers, the problem could be fixed by formatting only.
Hallo
For each Column:
Select Column → Data → Text in Columns → seperated by:
[x] spaceand[x] others: B$
in the PreView below select the unneeded Columns and chooseHide…insteadStandard
Hello,
related to my comment and if your data are in fact text, use Find & Replace (which doesn’t require extra helpers columns)
- Select all cells having text
$### B -
Edit -> Find and Replace
Find:B(there should be a space character prepending characterB)
Replace: leave empty
Other Options 1:[x] Current selection only - Click
Replace All - Close dialog
-
Right click -> Format cellsand provide the format you want to have for your - now numerical - data
Tested using LibreOffice:
Version: 7.1.3.2 / LibreOffice Community
Build ID: 47f78053abe362b9384784d31a6e56f8511eb1c1
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: en-US (en_US.UTF-8); UI: en-US; Calc: threaded
Hope that helps.
Using a spreadsheet as a shovel technique (i.e. no macro built) . . .
If each entry is fixed at 4 characters in length, insert a column right
Select the first cell with data to be modified.
In Mac: from main menu bar, Insert/Formula/ (find it yourself in Win :))
Function Wizard opens.
Check the top left selector shows Functions, then select the Category to Text.
Scroll down and double-click on Left - note the new entry boxes in the right area.
Note the insertion point In the upper Text box: it wants to know ‘which text’. Click in the top cell on your spreadsheet with the data. The Text entry box now shows the address of the cell that has the numbers and alpha characters.
Now activate the Number input box with a click. It wants to know how many characters on the Left that you want. 4, wasn’t it this time?
When you click OK the formula is entered into the cell with the data, and calculated. Bingo. Your numbers plus the ‘$’ symbol.
Select that cell. Scroll to the bottom of the column of data and Shift-click on the last data entry. The column of data should now be selected.
Go to menu bar Sheet/Fill/Fill Down (Note the shortcut,) Your formula is entered into every selected cell, & calculated.
It is good spreadsheet practice to NEVER mix data types. Those '$" signs mean to a spreadsheet the data is garbage.
You should label the column header as ‘info, $’ and only have numerals as the data. Format the column (Cmd-1) Numbers, as currency. Let the code do the work. There are your local currency symbols, wherever you are.
To get rid of the $ signs?
Try exploring the MID function in another new column.
When you’ve succeeded with stripping the $s out, you’ll have rock solid munchable data, but . . .
. . . those numbers on-screen are really only the results of a calculation.
To set it all in stone, now select those numbers in their column, then activate the top cell in the next row to the right.
From Edit/Paste Special/ select unformatted text.
NOW you have clean data to work with, and you can safely delete the original column of data, and the other two calculating columns.
When you get a handle on this lot, and on how to nest formulae, you will be able to do all this from just the one cell.
When you really get on top of things you will build a macro somewhere in the spreadsheet that goes off to where you have been stripping characters, and will step down that list by itself, performing the entire procedure at your command.
Cheers,
J
e&oe
The problem was fixed using find and replace. Thank you all for your help.