edit a cell

[abbigale]

I need to delete the fourth number in a series of cells all in the same column. Is that possible?

 

[PaddyD]

every fourth? starting at which cell? for how many cells?

More info please!

paddy

 

[SamS]

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]

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]

I tried Sam's approach and it worked. Thanks for all your help, it is greatly appreciated.