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.

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

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

[email protected]
[email protected] with @tiscali.co.uk


"anorton" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>>
>>
>>

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
[email protected]
"anorton" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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

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

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

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

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

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

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
>

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