make "F51668H200AU" into "F5166-8H200AU"
 

I need to add a - after the first 5 digits can get it to work with numbers
but not with letters & numbers

make "F51668H200AU" into "F5166-8H200AU"
 

Try:

=LEFT(A1,5) & "-" & RIGHT(A1,LEN(A1)-5)
--
Gary's Student


"fitzu" wrote:

I need to add a - after the first 5 digits can get it to work with numbers
but not with letters & numbers

make "F51668H200AU" into "F5166-8H200AU"
 

Hi Fitzu, try this

=REPLACE(A1,6,0,"-")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570567

make "F51668H200AU" into "F5166-8H200AU"
 

"Gary''s Student" wrote:

Try:

=LEFT(A1,5) & "-" & RIGHT(A1,LEN(A1)-5)
--
Gary's Student


"fitzu" wrote:

I need to add a - after the first 5 digits can get it to work with numbers
but not with letters & numbers

Hey How abt if I want to do the other way round, get rid of the dash"-" in
between?

make "F51668H200AU" into "F5166-8H200AU"
 

EditReplace

What: -
With: nothing


Gord Dibben MS Excel MVP

On Thu, 10 Aug 2006 16:35:02 -0700, Cruz wrote:



"Gary''s Student" wrote:

Try:

=LEFT(A1,5) & "-" & RIGHT(A1,LEN(A1)-5)
--
Gary's Student


"fitzu" wrote:

I need to add a - after the first 5 digits can get it to work with numbers
but not with letters & numbers

Hey How abt if I want to do the other way round, get rid of the dash"-" in
between?

make "F51668H200AU" into "F5166-8H200AU"
 

"fitzu" wrote:

I need to add a - after the first 5 digits can get it to work with numbers
but not with letters & numbers

I have tried both sugestion but i could no get it to work if i did as
formula got an error if i tried in formatting did not work . for numbers only
I used #####"-"##### is there a wildcard i could use instead for letters or
am i inputing the formulas in to the wrong area

make "F51668H200AU" into "F5166-8H200AU"
 

I don't think you can achieve this with formatting, my formula
suggestion above involves using that formula in another cell


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570567