Need another quick way in Excel!?

Question

Ok, so I have over 1000 rows of figures, example 123 45678
I want to delete the 123, but this changes from row to row and I want to keep the remaining 5 digits, how??? Thanks ( & to those who helped with my last question!)

Example: column A:

123 45678
124 45679
125 45680

I want to get rid of the first 3 numbers and keep the 5 remaining digits for over 1000 rows. I don't know how macros work... :(

Answer 1

-Data | Text to Columns...
-Choose Fixed Width
-Click Next
-Set break at 3 character (or 4 if there really is a space)
-Make sure to remove all other breaks
-Click Next
-Select the first column (if not already selected) and choose 'Do Not Import Column (skip)'
-Click Finish.

Answer 2

If the 123 carries on all the way down go to data at the top and use text to columns. Make sure you have a free column to the right of this to split it into. With this you can put the 123 in a seperate column to the rest. You can then delete the 123 column if not needed.

Hope this helps

Answer 3

If the first 3 numbers change as in your example and are separated by a space you could use the following to delete them.

Under the File menu select Replace. In the Find What box enter three asterisks *** followed by a space, the space is really important, if you leave it out you'll delete everything. Now in the Replace With box make sure its blank. Then click replace all.

Job done - hope it helps remember to try it on copy of your file not the original, just in case.

Answer 4

You can do this with an Excel macro. You can easily record the macro by preserving the last 5 digits. Make the Macro as a “relative macro” and then run it for over 1000 records. It’ll work.

Answer 5

Ok you can use the Mid function as some ppl have already suggested or you can use the 'Right' function as follows:
Assuming your numbers are in Column 'A', Highlight Column 'B' and insert new column, in Cell B1 type:
=Right(A1,5)
Then copy/paste or drag formula down column.
If you want to get rid of your previous column 'A' however you would get a '#REF!' in all of your formulas...so in that case proceed as follows:
First insert formula as above, next select all of Column 'B', then 'Edit', 'Copy'. Next CLICK in Cell B1, press right-click, then select 'Paste Special'. In the dialog that pops up select 'Values' and press ok. (You wont see any visible changes but now your cells in 'B' no longer contain any formulas but the actual results)
You may now safely delete the old Column 'A'

Answer 6

Easy,

select your records by dragging mouse pointer over them, with left button pressed,

then click on menu item Edit on top of the Excel screen, followed by clicking at Replace,

a sub-form will pop-up,

enter 123 in field labelled as Find what: and leave the second field labelled as Replace with: blank, and hit the button on the same sub-form labelled as Replace All.

It should do the job.

Answer 7

Take a look at the MID() function.

The space makes the number a text field. Use parameters 5, 5 should do it.

Answer 8

click in the fx box and it should highlight the whole lot. Then use the arow key to go to the number you want to delete and then backspace

Is this what you meant?

Answer 9

column A ..................... column B
row1: 123 45678 ..................... row1: =mid(a1,5,10)
row2: 124 45679 ..................... row2: =mid(a2,5,10)
row3: 125 45680 ..................... row3: =mid(a3,5,10)
just copy cell b1 and paste it over the rest of the cells in column B.
so: mid(a1,5,10) means: you're starting in the middle, value from cell A1, get value from 5th charachter, and display the next 10 numbers (charachters) i.e. if you wanted to show just numbers 5 and 6 then: =mid(a1,7,2)

Answer 10

confident it may. it is finished by using installation a formula in one workbook that references particular cells in the different. once you're accustomed to formula, you would understand which you will click into any cellular in any workbook to set it as component to the standards on your function. i exploit a "networkdays" calculation frequently, so I extremely have one workbook that in simple terms lists holiday journeys. on each and every occasion i prefer the networkdays calculation, i exploit my "holiday journeys" workbook by way of fact the foundation for the formula.