# 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

Hey everybody.

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.

#### cristobal03

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.

#### cristobal03

[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.

#### ProphetX

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

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?

#### Zack Barresse

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")?

#### Zack Barresse

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.

#### cristobal03

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

• 50.8 KB Views: 36
• 51.2 KB Views: 37
• 52.5 KB Views: 34

#### smooth

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. That worked perfectly. The pictures really helped. lol

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

Thanks also to the other replies.

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?

#### cristobal03

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.

#### Zack Barresse

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

Thanks. I'll have to keep that combo in mind.

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.

As Seen On