LinkBack URL
About LinkBacks
Need to remove the 4th number in every cell of a column. I don't know how to do it. is there a way...... Any help would be greatly appreciated
Need to remove the 4th number in every cell of a column. I don't know how to do it. is there a way...... Any help would be greatly appreciated
Try using a formula next to the column
=REPLACE(A1,4,1,"")
where A1 contains first number to work with.
copy formula down the columnm
Once finished copy the new column and go to first column, then Edit|PAste Special and select Values.. then Ok.
Now you can delete the formula column
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I read this differently than NBVC, if you want every 4th cell deleted;
in another column, dragged downwhere A1 is your first data cell in your column of interest. Then, copy and paste special values over your original data. Is this what you wanted?
Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Let me say it another way.
I have a column of cells containing 10 digit numbers I want to remove the 4th
digit from every number in every cell
Thanks
Let me say it another way.
I have a column of cells containing 10 digit numbers I want to remove the 4th
digit from every number in every cell
Thanks
=left(a1,3) & mid(a1, 5, 6)
Entia non sunt multiplicanda sine necessitate
If the data is always 10 digits, another way to do this is to combine the "Left" and "Right" functions. Here is an example:
Your number 1234567890
The formula: =left(a1,3) & right(a1,6)
Some people have a hard time with the mid function.
Doing this will take the first 3 characters from the left and the first 6 characters from the right. Keep in mind that a space is counted. So, "12 34" would have 5 characters (since the space is counted).
The formula I gave will do thatOriginally Posted by steadfast199
=REPLACE(A1,4,1,"")
If you want the resultant to be a number.. then add a +0
=REPLACE(A1,4,1,"")+0
Last edited by NBVC; 10-09-2009 at 04:53 PM. Reason: I hade a _0 instead of a +0 ...ooops
I vote for NBVC's solution -- hadn't noted it was the very first suggestion
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks