HELP WITH FORMULA

  • Thread starter Thread starter SUZZI
  • Start date Start date
S

SUZZI

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?
 
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)
 
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
Click to expand...
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
 
Back
Top