Prev: Create new records from existing ones based on 'rule'?
Next: Search, Match, Pull
From: Derek M on 27 May 2010 17:54
Hi all again

I need the address to be 18 characters long i.e. if the address is over 18
characters then chop it, if its under, then add spaces

Is this possible?

Thanks as always

Derek
From: Brad on 27 May 2010 18:06
=left(a1&rept(" ",18),18)

This will add spaced at the end

if you want spaces in front
=right(rept(" ",18)&a1,18)

--
Wag more, bark less


"Derek M" wrote:

> Hi all again
>
> I need the address to be 18 characters long i.e. if the address is over 18
> characters then chop it, if its under, then add spaces
>
> Is this possible?
>
> Thanks as always
>
> Derek
From: Steve Dunn on 28 May 2010 08:54
that second formula would result in the last 18 characters of the address if
A1 was already longer than that.

=left(rept(" ",max(0,18-len(a1)))&a1,18)



"Brad" <Brad(a)discussions.microsoft.com> wrote in message
news:764D703C-72F1-42F7-A613-09947C52E2E5(a)microsoft.com...
> =left(a1&rept(" ",18),18)
>
> This will add spaced at the end
>
> if you want spaces in front
> =right(rept(" ",18)&a1,18)
>
> --
> Wag more, bark less
>
>
> "Derek M" wrote:
>
>> Hi all again
>>
>> I need the address to be 18 characters long i.e. if the address is over
>> 18
>> characters then chop it, if its under, then add spaces
>>
>> Is this possible?
>>
>> Thanks as always
>>
>> Derek

From: Derek M on 1 Jun 2010 08:06
Fantastic, thanks guys

"Steve Dunn" wrote:

> that second formula would result in the last 18 characters of the address if
> A1 was already longer than that.
>
> =left(rept(" ",max(0,18-len(a1)))&a1,18)
>
>
>
> "Brad" <Brad(a)discussions.microsoft.com> wrote in message
> news:764D703C-72F1-42F7-A613-09947C52E2E5(a)microsoft.com...
> > =left(a1&rept(" ",18),18)
> >
> > This will add spaced at the end
> >
> > if you want spaces in front
> > =right(rept(" ",18)&a1,18)
> >
> > --
> > Wag more, bark less
> >
> >
> > "Derek M" wrote:
> >
> >> Hi all again
> >>
> >> I need the address to be 18 characters long i.e. if the address is over
> >> 18
> >> characters then chop it, if its under, then add spaces
> >>
> >> Is this possible?
> >>
> >> Thanks as always
> >>
> >> Derek
>
 | 
Pages: 1
Prev: Create new records from existing ones based on 'rule'?
Next: Search, Match, Pull