Tech Support Guy banner
Status
Not open for further replies.

Solved: Excel 2003: Replacing Large Numbers with Smaller Numbers

1K views 11 replies 4 participants last post by  smooth 
#1 ·
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.
 
#2 ·
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.
 
#4 ·
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.
 
#5 ·
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?
 
#6 ·
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")?
 
#7 ·
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. ;)
 
#8 ·
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

#9 ·
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?
 
#11 ·
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. ;)
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top