edit a cell

abbigale

New Member
Joined
Oct 28, 2002
Messages
15
I need to delete the fourth number in a series of cells all in the same column. Is that possible?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
every fourth? starting at which cell? for how many cells?

More info please!

paddy
 

SamS

Well-known Member
Joined
Feb 17, 2002
Messages
542
Hi abbigale - I get all hung up on formula dealing with length of text but you will probably get a response from some very noteworthy people on this site. I usually go to DATA>TEXT TO COLUMNS select FIXED WIDTH and on the nex panel insert a column (click between the 2 columns)after the 3rd column and also after the fourth column. Note you will need 2 spare columns to the right of your data when you do this. Once this is done in column D (supposing your data was in column A enter +A1 & C1 to join them back together then copy and paste the column as values. Clumsy but works well.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
From a PM:
"Each number is 12 digits long. The extra number, always being a zero is in the fourth place of the 12 digit number. All of these numbers are in the same column and the number of cells varies depending on the imported data. "

1) You'd be better of sending this sort of thing direct to the board - then everyone gets to see the info & the problem is probably solved quicker

2) You could use the method suggested by SamS (have you tried it?)

3) You could use a formula that extracts the relevant portions of the string, something like:

=LEFT(A1,3)&MID(A1,5,LEN(A1)-4)

or

=(LEFT(A1,3)&MID(A1,5,LEN(A1)-4))+0

if you wish to retain the strings as numbers.

(assuming all strings are at least 4 characters long)

4) use an appropriate macro.

paddy
 

abbigale

New Member
Joined
Oct 28, 2002
Messages
15
I tried Sam's approach and it worked. Thanks for all your help, it is greatly appreciated.
 

Forum statistics

Threads
1,144,358
Messages
5,723,889
Members
422,524
Latest member
wirkkarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top