Find & replace question.....I believe

A

anorton

Hi all,

I would like to see if there is a way to modify the numbers in a cell to
only show a partial? For example:

8709300242_4576
8710010110_4550
8710010157_4580
8710050132_4564

I want to only show:

4576
4550
4580
4564

Thanks.
 
A

Alan

If its always the last four digits,
=RIGHT(A1,4)
then copy > paste special > values to lose the formulas,
Regards,
Alan.
 
A

anorton

I'm sorry, maybe I wasn't clear. There is no formula. Just the exact numeric
value shown.

And yes, I just want the last four digits to just show in that cell.
 
A

Alan

I meant that if the values were in say A1 to A100 then in B1 enter that
formula and drag it down to B100, then Copy, PasteSpecial, values to get rid
of the formulas in B1:B100. You can then copy column B and paste to cloumn A
if you want to to overwrite the original data,
Regards,
Alan.
 
D

Don Guillett

put this in the next column and copy down.
=RIGHT(I2,LEN(I2)-FIND("_",I2))
Or, if you want to eliminate the numbers to the left of _ and have them in
the cell they were originally you will need a macro.
Please advise
 
A

anorton

I'm sorry, again. But I'm not understanding what you mean. If there's a way
to post a screen shot of the worksheet area I'm working in, I would.
 
R

Ron Coderre

Try this:

Select the range of values to be impacted.

From the Excel main menu:
<edit><replace>
Find what: *_
Replace with: (leave this blank)
Click the [Replace All] button

That should remove everything up to, and including, the underscore character.

Note: that technique will actually change the cell values.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
G

Gary''s Student

There is a way to modify the cells so that all the characters are there, but
only the last four charaters are visible.


The way to do this is to high-light the first 11 characters and change the
color of the font to match the color of the background.

All 15 characters will be there, but only the last four will be visible in
printing or screen-shots.

Have a pleasant weekend !
 
G

Gary''s Student

If you have many cells that require re-formatting, then enter and run this
small macro:


Sub Macro1()
Dim r As Range, nLastRow As Long
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
For l = 1 To nLastRow
Cells(l, 1).Characters(Start:=1, Length:=11).Font.ColorIndex = 2
Next
End Sub

The macrom as coded, operates on column A and sets the color of the first 11
charactera to white.
 
D

Dave Peterson

Select the range
edit|replace
what: *_
with: (leave blank)
replace all

However, this will change:
8709300242_0023
to just plain old 23
But you could give the cells a custom format of 0000.
 
A

Alan

Good One!
Easy and no formulas needed, we live and learn,
Regards,
Alan
Ron Coderre said:
Try this:

Select the range of values to be impacted.

From the Excel main menu:
<edit><replace>
Find what: *_
Replace with: (leave this blank)
Click the [Replace All] button

That should remove everything up to, and including, the underscore
character.

Note: that technique will actually change the cell values.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


anorton said:
Hi all,

I would like to see if there is a way to modify the numbers in a cell to
only show a partial? For example:

8709300242_4576
8710010110_4550
8710010157_4580
8710050132_4564

I want to only show:

4576
4550
4580
4564

Thanks.
Click to expand...
Click to expand...
 
R

Ron Coderre

Dave

If *_ is replaced wth an apostrophe, any leading zeros after the underscore
will be preserved. (although, the remaining characters will be text...if
that's OK with the OP)

8710050132_0064 would become 0064

***********
Best Regards,
Ron

XL2002, WinXP
 
D

Dave Peterson

Another nice option.

Ron said:
Dave

If *_ is replaced wth an apostrophe, any leading zeros after the underscore
will be preserved. (although, the remaining characters will be text...if
that's OK with the OP)

8710050132_0064 would become 0064

***********
Best Regards,
Ron

XL2002, WinXP
Click to expand...
 
Top