Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell with a formula
Hello,
In would like help with adding a dash to a series of numbers. I know I can format with special formatting using 0-00. However the result does return a hit when I search with the -00. Can you offer a formula that will add the dash and yield a result that can be searched. Thanks for the help (example below) Now Needs to be 38605.................386-05 118306...............1183-06 511602...............5116-02 553404...............5534-04 553703...............5537-03 597309...............5973-09 597704...............5977-04 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell with a formula
Hi,
You would need a macro to do it in the same column but you can format your numbers to a different column like this and hide the original column. Alternatively you can copy this column and paste it back over the originial using paste special - values. =LEFT(A1,LEN(A1)-2)&"-"&RIGHT((A1),2) Mike "Jim" wrote: Hello, In would like help with adding a dash to a series of numbers. I know I can format with special formatting using 0-00. However the result does return a hit when I search with the -00. Can you offer a formula that will add the dash and yield a result that can be searched. Thanks for the help (example below) Now Needs to be 38605.................386-05 118306...............1183-06 511602...............5116-02 553404...............5534-04 553703...............5537-03 597309...............5973-09 597704...............5977-04 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell with a formula
=LEFT(A1,LEN(A1)-2)&"-"&RIGHT(A1,2)
To search with Edit Find you need to Copy PasteSpecial Values Regards, Alan. "Jim" wrote in message ... Hello, In would like help with adding a dash to a series of numbers. I know I can format with special formatting using 0-00. However the result does return a hit when I search with the -00. Can you offer a formula that will add the dash and yield a result that can be searched. Thanks for the help (example below) Now Needs to be 38605.................386-05 118306...............1183-06 511602...............5116-02 553404...............5534-04 553703...............5537-03 597309...............5973-09 597704...............5977-04 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell with a formula
If the OP wants to do it this way, then here is a simpler formula that he
can use... =TEXT(A1,"0-00") -- Rick (MVP - Excel) "Mike H" wrote in message ... Hi, You would need a macro to do it in the same column but you can format your numbers to a different column like this and hide the original column. Alternatively you can copy this column and paste it back over the originial using paste special - values. =LEFT(A1,LEN(A1)-2)&"-"&RIGHT((A1),2) Mike "Jim" wrote: Hello, In would like help with adding a dash to a series of numbers. I know I can format with special formatting using 0-00. However the result does return a hit when I search with the -00. Can you offer a formula that will add the dash and yield a result that can be searched. Thanks for the help (example below) Now Needs to be 38605.................386-05 118306...............1183-06 511602...............5116-02 553404...............5534-04 553703...............5537-03 597309...............5973-09 597704...............5977-04 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell with a formula
As I posted back to Mike, this is a simpler formula to do the same thing...
=TEXT(A1,"0-00") -- Rick (MVP - Excel) "Alan" wrote in message ... =LEFT(A1,LEN(A1)-2)&"-"&RIGHT(A1,2) To search with Edit Find you need to Copy PasteSpecial Values Regards, Alan. "Jim" wrote in message ... Hello, In would like help with adding a dash to a series of numbers. I know I can format with special formatting using 0-00. However the result does return a hit when I search with the -00. Can you offer a formula that will add the dash and yield a result that can be searched. Thanks for the help (example below) Now Needs to be 38605.................386-05 118306...............1183-06 511602...............5116-02 553404...............5534-04 553703...............5537-03 597309...............5973-09 597704...............5977-04 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell with a formula
Rick,
For some reason I have this mental block with TEXT and only think of using it with dates. Thanks. Mike "Rick Rothstein" wrote: If the OP wants to do it this way, then here is a simpler formula that he can use... =TEXT(A1,"0-00") -- Rick (MVP - Excel) "Mike H" wrote in message ... Hi, You would need a macro to do it in the same column but you can format your numbers to a different column like this and hide the original column. Alternatively you can copy this column and paste it back over the originial using paste special - values. =LEFT(A1,LEN(A1)-2)&"-"&RIGHT((A1),2) Mike "Jim" wrote: Hello, In would like help with adding a dash to a series of numbers. I know I can format with special formatting using 0-00. However the result does return a hit when I search with the -00. Can you offer a formula that will add the dash and yield a result that can be searched. Thanks for the help (example below) Now Needs to be 38605.................386-05 118306...............1183-06 511602...............5116-02 553404...............5534-04 553703...............5537-03 597309...............5973-09 597704...............5977-04 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell with a formula
Formatting a cell does not change the value in the cell, only the way it is
displayed, so your search won't work if the *contents* (as distinct from the *display*) don't match your search criteria. If you want to search for a string including the hyphen, you probably need a helper column such as =TEXT(A2,"0-00") -- David Biddulph "Jim" wrote in message ... Hello, In would like help with adding a dash to a series of numbers. I know I can format with special formatting using 0-00. However the result does return a hit when I search with the -00. Can you offer a formula that will add the dash and yield a result that can be searched. Thanks for the help (example below) Now Needs to be 38605.................386-05 118306...............1183-06 511602...............5116-02 553404...............5534-04 553703...............5537-03 597309...............5973-09 597704...............5977-04 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a cell with a formula
I posted the reply before yours was visible, had I'd seen your reply I would
not have responded. It is a better way to do it. "Rick Rothstein" wrote in message ... As I posted back to Mike, this is a simpler formula to do the same thing... =TEXT(A1,"0-00") -- Rick (MVP - Excel) "Alan" wrote in message ... =LEFT(A1,LEN(A1)-2)&"-"&RIGHT(A1,2) To search with Edit Find you need to Copy PasteSpecial Values Regards, Alan. "Jim" wrote in message ... Hello, In would like help with adding a dash to a series of numbers. I know I can format with special formatting using 0-00. However the result does return a hit when I search with the -00. Can you offer a formula that will add the dash and yield a result that can be searched. Thanks for the help (example below) Now Needs to be 38605.................386-05 118306...............1183-06 511602...............5116-02 553404...............5534-04 553703...............5537-03 597309...............5973-09 597704...............5977-04 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting if a cell contains a formula | Excel Worksheet Functions | |||
cell formatting with formula | Excel Worksheet Functions | |||
Formatting Cell that Contains If/Then Formula | Excel Discussion (Misc queries) | |||
cell formula or formatting | Excel Worksheet Functions | |||
Formatting a formula within a cell | Excel Discussion (Misc queries) |