Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace - problem with automatically changing formatting | Excel Discussion (Misc queries) | |||
Find and Replace | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
how to find and replace a symbol in my worksheet | Excel Discussion (Misc queries) |