Format Numbers

[ernestgoh]

hi i am trying to obtain numbers only

for example:
44-566 = 44566
23_65 = 2365

is there such a function? thanks.

ernest

[oldchippy]

hi i am trying to obtain numbers only

for example:
44-566 = 44566
23_65 = 2365

is there such a function? thanks.

ernest

Hi ernestgoh,

Try this formula

=CONCATENATE(LEFT(A1,2),RIGHT(A1,3))*1

this assumes it is always 2 characters left, 3 characters right, adapt to suit

oldchippy

[ernestgoh]

Hi ernestgoh,

Try this formula

=CONCATENATE(LEFT(A1,2),RIGHT(A1,3))*1

this assumes it is always 2 characters left, 3 characters right, adapt to suit

oldchippy

hi oldchippy,

thanks for your response. trouble is the format is not fixed. thanks again!

ernest

[oldchippy]

hi oldchippy,

thanks for your response. trouble is the format is not fixed. thanks again!

ernest

Hi ernestgoh,

It's never simply is it? Try this

=CONCATENATE(LEFT(A1,SEARCH("-",A1,1)-1),RIGHT(A1,SEARCH("-",A1,1)-1))*1

This will put it into number format.

oldchippy

[ernestgoh]

Hi ernestgoh,

It's never simply is it? Try this

=CONCATENATE(LEFT(A1,SEARCH("-",A1,1)-1),RIGHT(A1,SEARCH("-",A1,1)-1))*1

This will put it into number format.

oldchippy

still doesn't really work. would you know a function that can count how many characters it has?

for example,
45_674 = 6 characters
123-456 = 7 characters

thanks again for trying! ernest

[oldchippy]

still doesn't really work. would you know a function that can count how many characters it has?

for example,
45_674 = 6 characters
123-456 = 7 characters

thanks again for trying! ernest

Very sorry ernestgoh,

this one does hopefully, it's getting late in the day, brain failing!

=CONCATENATE(LEFT(A1,SEARCH("-",A1,1)-1),MID(A1,SEARCH("-",A1,1)+1,LEN(A1)))*1

oldchippy

[ernestgoh]

Very sorry ernestgoh,

this one does hopefully, it's getting late in the day, brain failing!

=CONCATENATE(LEFT(A1,SEARCH("-",A1,1)-1),MID(A1,SEARCH("-",A1,1)+1,LEN(A1)))*1

oldchippy

hey youngchippy!

it works a treat!! thanks for all your help.

ernest

[oldchippy]

We got there in the end - thanks for the feedback