Solved: Excel 2003: Replacing Large Numbers with Smaller Numbers

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

smooth

Thread Starter
Joined
Sep 26, 2005
Messages
4,029
First Name
Garrett
Hey everybody. :D

I don't know if the title is a good one for this problem, but here goes.

I have a column that has numbers in this format: 54321-1234

Now, I want to take off the last 4 numbers, and the dash, from all the numbers in the column, using a formula. So that the output would be: 54321.

I want it so the first 5 numbers stay the same, but the dash and last 4 are gone. Is this possible?

Thanks.
 
Joined
Aug 5, 2005
Messages
3,086
The column is text, correct? You can use the LEFT worksheet function.

LEFT(string or range, length)

So, if the range was A:A, and you wanted the output in column B, the formula for cell B1 would be

=LEFT($A1, 5)

Then you'd just do a drag-copy for the range you were interested in trimming.

HTH

chris.
 
Joined
Aug 5, 2005
Messages
3,086
[bump]

That won't change the value of the cells in column A. If that's what you're interested in doing, we'll have to try something else.

chris.
 
Joined
May 17, 2005
Messages
28
If all the numbers are the same after the hyphen, you could do a find and replace and replace the text with nothing. e.g. Find: -1234 Replace: [blank] Replace All.

Alternatively, you can copy that column into Word, convert table to text, convert text to 2-column table using hyphens as cell dividers, copy column 1 back to excel.

As for a macro in Excel to do this for you, I am not able to assist with that.

Hope this helps.
 

smooth

Thread Starter
Joined
Sep 26, 2005
Messages
4,029
First Name
Garrett
cristobal03 said:
The column is text, correct? You can use the LEFT worksheet function.

LEFT(string or range, length)

So, if the range was A:A, and you wanted the output in column B, the formula for cell B1 would be

=LEFT($A1, 5)
That worked great Chris. Thanks.

Then you'd just do a drag-copy for the range you were interested in trimming.
How would I do this? I want the formula to work for all of Column B. How do I get the formula for that column to do that?
 
Joined
Jul 25, 2004
Messages
5,456
Copy the new formula(s), press Alt + E, S, V, Enter. Either delete the original value(s) or copy the new one's over the old one's.

Are these zip codes? Did you know there is a custom format for zip codes, with and without the last four ("-0000")?
 
Joined
Jul 25, 2004
Messages
5,456
ProphetX said:
Alternatively, you can copy that column into Word, convert table to text, convert text to 2-column table using hyphens as cell dividers, copy column 1 back to excel.
Oh yuck. Please no. Excel has a Text to Columns feature in the Data menu. ;)
 
Joined
Aug 5, 2005
Messages
3,086
When you activate a cell, it's marked by a thicker border with a dragging target (or whatever it's called) in the lower right-hand corner. With that formula in cell B1, click the target in the lower right-hand corner of the cell and drag the selection down the column for the desired length.

chris.
 

Attachments

smooth

Thread Starter
Joined
Sep 26, 2005
Messages
4,029
First Name
Garrett
cristobal03 said:
When you activate a cell, it's marked by a thicker border with a dragging target (or whatever it's called) in the lower right-hand corner. With that formula in cell B1, click the target in the lower right-hand corner of the cell and drag the selection down the column for the desired length.
Thanks Chris. :D That worked perfectly. The pictures really helped. lol

Once again you have helped me through a problem. Thanks again. :D

Thanks also to the other replies. :D

firefytr, I couldn't get the Alt + E, S, V, Enter to work right. I must have been hitting the keys wrong or something. Was it a paste special?
 
Joined
Aug 5, 2005
Messages
3,086
Yeah, that would paste the values rather than the formula. If you try that now, selecting the column with the new formula, then targetting a new, blank column, that will paste the values not the formula.

In case you wanted to actually trim the original data.

chris.
 
Joined
Jul 25, 2004
Messages
5,456
Yes, it's Paste Special/Values.

It's:

Press (together) Alt + E
Let go
Press the S key
Press the V key
Press Enter

All together:
Alt + E, S, V

Note the + sign and , signs; it's not all one combo. I don't know what I did before learning this combo. ;)
 

smooth

Thread Starter
Joined
Sep 26, 2005
Messages
4,029
First Name
Garrett
Thanks. I'll have to keep that combo in mind. :D

Thanks again to everyone for helping me out.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top