Hi

I have data on Column a1 as:

A1

1HOTEL
0PURCHASER TOWN/
POST OFFICE
COMMUNITY
0

Data format is general

I want to remove 1,0 i am using this formaula but it does
not work for POST OFFICE & COMMUNITY

=IF(OR(MID(A1,1,1)=1,MID(A1,1,1)=0,MID(A1,1,1)="") ,A1,MID
(31,2,60))

can any one Help on this one please

Thanks

Have you looked at using Edit=Replace

Are the numbers 1 and 0 found in other locations in the text string?

--
Regards,
Tom Ogilvy

"SUZZI" wrote in message
...
Hi

I have data on Column a1 as:

A1

1HOTEL
0PURCHASER TOWN/
POST OFFICE
COMMUNITY
0

Data format is general

I want to remove 1,0 i am using this formaula but it does
not work for POST OFFICE & COMMUNITY

=IF(OR(MID(A1,1,1)=1,MID(A1,1,1)=0,MID(A1,1,1)="") ,A1,MID
(31,2,60))

can any one Help on this one please

Thanks

If Tom's suggest is no good... It seems your formula is doing the opposite
of what you say you want. Anyway maybe:

=IF(OR(LEFT(A1,1)="1",LEFT(A1,1)="0"),MID(A1,2,100 ),A1)

--
Jim Rech
Excel MVP

Thanks for reply

0/1 dont repeat else where in the string


-----Original Message-----
Have you looked at using Edit=Replace

Are the numbers 1 and 0 found in other locations in the
text string?

--
Regards,
Tom Ogilvy

"SUZZI" wrote in
message
...
Hi

I have data on Column a1 as:

A1

1HOTEL
0PURCHASER TOWN/
POST OFFICE
COMMUNITY
0

Data format is general

I want to remove 1,0 i am using this formaula but it
does
not work for POST OFFICE & COMMUNITY

=IF(OR(MID(A1,1,1)=1,MID(A1,1,1)=0,MID(A1,1,1)
=""),A1,MID
(31,2,60))

can any one Help on this one please

Thanks




.

Thanks for reply

Your formula helped thanks once again

-----Original Message-----
If Tom's suggest is no good... It seems your formula is
doing the opposite
of what you say you want. Anyway maybe:

=IF(OR(LEFT(A1,1)="1",LEFT(A1,1)="0"),MID(A1,2,10 0),A1)

--
Jim Rech
Excel MVP


.

Thanks for reply

Your formula helped thanks once again

-----Original Message-----
If Tom's suggest is no good... It seems your formula is
doing the opposite
of what you say you want. Anyway maybe:

=IF(OR(LEFT(A1,1)="1",LEFT(A1,1)="0"),MID(A1,2,10 0),A1)

--
Jim Rech
Excel MVP


.

On Wed, 10 Dec 2003 05:17:11 -0800, "SUZZI"
wrote:

Hi

I have data on Column a1 as:

A1

1HOTEL
0PURCHASER TOWN/
POST OFFICE
COMMUNITY
0

Data format is general

I want to remove 1,0 i am using this formaula but it does
not work for POST OFFICE & COMMUNITY

=IF(OR(MID(A1,1,1)=1,MID(A1,1,1)=0,MID(A1,1,1)="" ),A1,MID
(31,2,60))

can any one Help on this one please

Just another solution:

=RIGHT(L1,MIN(LEN(SUBSTITUTE(LEFT(L1,1),{"0","1"}, "")&RIGHT(L1,LEN(L1)-1))))

Note that {"0","1"} is an array constant. And you could add any other leading
character to this array.


--ron