Solved: Excel 2003: Replacing Large Numbers with Smaller Numbers

Discussion in 'Business Applications' started by smooth, Dec 20, 2005.

Not open for further replies.

Joined:
Sep 26, 2005
Messages:
4,029
First Name:
Garrett
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.

2. cristobal03

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.

3. cristobal03

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.

4. ProphetX

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.

Joined:
Sep 26, 2005
Messages:
4,029
First Name:
Garrett
That worked great Chris. Thanks.

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. Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
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. Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Oh yuck. Please no. Excel has a Text to Columns feature in the Data menu.

8. cristobal03

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.

Attached Files:

File size:
50.8 KB
Views:
36
File size:
51.2 KB
Views:
37
• Image3.jpg
File size:
52.5 KB
Views:
34

Joined:
Sep 26, 2005
Messages:
4,029
First Name:
Garrett
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?

10. cristobal03

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.

11. Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
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.

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

Thanks again to everyone for helping me out.

As Seen On