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

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

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

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

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

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

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

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