SUBSTITUTE(Outward!B5,"-","0")+0

[Guest]

SUBSTITUTE(Outward!B5,"-","0")+0

from 1-100-1 to 0110001 please

Ok I was kindly given the above formula Which I am grateful and useful.
Please can I Have it so it deletes the first - in 1-100 to be 1100
the end result woud be 0110001 from the original 1-100-1 value
in brief first - deleted, the second - replace with a 0.

Thankyou for your time on this issue.

 

[Biff]

Hi!

Try this:

Is 0110001 a numeric number or a text string?

="0"&SUBSTITUTE(SUBSTITUTE(Outward!B5,"-","",1),"-",0)

The above will return a text string.

If you want a numeric number:

=SUBSTITUTE(SUBSTITUTE(Outward!B5,"-","",1),"-",0)*1

Format the cell as CUSTOM - 0000000

Biff

 

[Vasant Nanavati]

=0&SUBSTITUTE(SUBSTITUTE(Outward!B5,"-","",1),"-",0,1)

 

[Vasant Nanavati]

The above will return a text string.

If you want a numeric number

Hi Biff:

I know it's late, but are there non-text strings and non-numeric numbers?
<vbg>

Just kidding!

Regards,

Vasant

 

[Dave Peterson]

A former co-worker once asked:

Is that the number Oh or the letter zero?

It still brings smiles.

 

[Biff]

Hmmm......

I don't get it !

Biff

Hi Biff:

I know it's late, but are there non-text strings and non-numeric numbers?
<vbg>

Just kidding!

Regards,

Vasant

 

[Guest]

Thankyou

Hi!

Try this:

Is 0110001 a numeric number or a text string?

="0"&SUBSTITUTE(SUBSTITUTE(Outward!B5,"-","",1),"-",0)

The above will return a text string.

If you want a numeric number:

=SUBSTITUTE(SUBSTITUTE(Outward!B5,"-","",1),"-",0)*1

Format the cell as CUSTOM - 0000000

Biff

 

[Vasant Nanavati]

Sorry, only my late-night sense of humor! Just ignore me <g>.