Find & replace question.....I believe
 

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.

Find & replace question.....I believe
 

If its always the last four digits,
=RIGHT(A1,4)
then copy paste special values to lose the formulas,
Regards,
Alan.
"anorton" wrote in message
...
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.

Find & replace question.....I believe
 

Try:

=RIGHT(A1,LEN(A1)-FIND("_",A1))

If you mean to alter the actual contents of the original cells then you
would need to do it with VBA

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"anorton" wrote in message
...
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.

Find & replace question.....I believe
 

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.

"Alan" wrote:

If its always the last four digits,
=RIGHT(A1,4)
then copy paste special values to lose the formulas,
Regards,
Alan.
"anorton" wrote in message
...
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.

Find & replace question.....I believe
 

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.
"anorton" wrote in message
...
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.

"Alan" wrote:

If its always the last four digits,
=RIGHT(A1,4)
then copy paste special values to lose the formulas,
Regards,
Alan.
"anorton" wrote in message
...
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.

Find & replace question.....I believe
 

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

--
Don Guillett
SalesAid Software

"anorton" wrote in message
...
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.

Find & replace question.....I believe
 

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.

"anorton" wrote:

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.

Find & replace question.....I believe
 

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" wrote:

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.

Find & replace question.....I believe
 

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 !

--
Gary's Student


"anorton" wrote:

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.

Find & replace question.....I believe
 

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.
--
Gary's Student


"anorton" wrote:

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.

Find & replace question.....I believe
 

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.




anorton wrote:

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.

--

Dave Peterson

Find & replace question.....I believe
 

Good One!
Easy and no formulas needed, we live and learn,
Regards,
Alan
"Ron Coderre" wrote in message
...
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" wrote:

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.

Find & replace question.....I believe
 

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


"Dave Peterson" wrote:

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.




anorton wrote:

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.

--

Dave Peterson

Find & replace question.....I believe
 

Another nice option.

Ron Coderre wrote:

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

"Dave Peterson" wrote:

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.




anorton wrote:

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.

--

Dave Peterson


--

Dave Peterson