How to Split Text depending upon lenght

[pra_kale]

Hi,

I have a one excel file having addresses in one cell including special characters. First I want to remove the special characters and then want to split text into 3 rows, But,  first 2 rows should contains only 20 characters each. Last row can contain remaining characters.

I able to remove special characters from the string. Help is required to split the string into 3 different rows.

Thanks in advance.

[sunny_talwar]

Like this?

Or do you need complete words within the 20 character limits in one cell?

[pra_kale]

Thanks sunny...

Yes I actually want complete words with 20 character limits in one cell..

[sunny_talwar]

This might not be the best way to do this, but seems to work

A:

LOAD *,

  RowNo() as Key,

  Len(Temp) as Temp2;

LOAD *,

  SubField(New_Address, ' ') as Temp;

LOAD *,

  PurgeChar(Address,'%,#,!,@,$,,') as New_Address;

LOAD Address

FROM

[chararcter_lenghth.xlsx]

(ooxml, embedded labels, table is Sheet1);

TempTable:

LOAD *,

  If(Cul >= Previous(Cul), Alt(Peek('SNo'), 1), RangeSum(Peek('SNo'), 1)) as SNo;

LOAD *,

  If(New_Address = Previous(New_Address), If(RangeSum(Temp2, Peek('Cul')) <= 20, RangeSum(Temp2, Peek('Cul')), Temp2), Temp2) as Cul

Resident A

Order By New_Address, Key;

Join (A)

LOAD New_Address,

  SNo,

  Concat(Temp, ' ') as New_Address1

Resident TempTable

Group By New_Address, SNo;

DROP Table TempTable;

[MarcoWedel · Accepted solution]

Hi,

maybe one solution might be also:

SET vIsLetter = (Upper($1)<>Lower($1));
SET vIsNumOrLetter = (IsNum($1) or $(vIsLetter($1)));

mapKeepNumLetter:
Mapping
LOAD *,
    If($(vIsNumOrLetter(Char)) or Char=' ', Char, '');
LOAD Chr(RecNo()) as Char
AutoGenerate 65535;

table1:
LOAD *,
    Mid(Address,Len(AddressRow1&AddressRow2)+1) as AddressRow3;
LOAD Address,
    AddressRow1,
    Left(AddressTemp,If(Index(Left(AddressTemp,20),' ',-1),Index(Left(AddressTemp,20),' ',-1),20)) as AddressRow2;
LOAD *,
    Mid(Address,Len(AddressRow1)+1) as AddressTemp;
LOAD Address,
    Left(Address,If(Index(Left(Address,20),' ',-1),Index(Left(Address,20),' ',-1),20)) as AddressRow1;
LOAD MapSubString('mapKeepNumLetter',Address) as Address
Inline [
Address
"ABC safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCsafalya chs., DOCYard, Kelva mandir road France @$!"
"ABCsafalyachs., DOCYard, Kelva mandir road France @$!"
"ABCsafalyachs.,DOCYard, Kelva mandir road France @$!"
"ABCD safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDE safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEF safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFG safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGH safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHI safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJ safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJK safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKL safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLM safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMN safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNO safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOP safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOPQ safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOPQR safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOPQRS safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOPQRST safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOPQRSTU safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOPQRSTUV safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOPQRSTUVW safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOPQRSTUVWX safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOPQRSTUVWXY safalya chs., DOCYard, Kelva mandir road France @$!"
"ABCDEFGHIJKLMNOPQRSTUVWXYZ safalya chs., DOCYard, Kelva mandir road France @$!"
];

hope this helps

regards

Marco

[antoniotiman]

Hi,

may be

Temp:
Load *,
Left(Address,FindOneOf(Address,' ',SubStringCount(Left(Address,21),' '))) as NewAddress;
LOAD IterNo() as RowNr,PurgeChar(Address,'%,#,!,@,$,,') as Address
FROM
[chararcter_lenghth.xlsx]
(ooxml, embedded labels, table is Sheet1)
While IterNo() <= 3;

Table:
NoConcatenate
Load RowNr,Address,
Pick(Match(RowNr,1,2,3),
NewAddress,
Left(Replace(Address,(NewAddress),''),FindOneOf(Replace(Address,(NewAddress),''),' ',SubStringCount(Left(Replace(Address,(NewAddress),''),21),' '))),
Replace(Replace(Address,Peek(NewAddress,-2),''),Peek(NewAddress),'')
) as NewAddress
Resident Temp;

Drop Table Temp;

Regards,

Antonio

[pra_kale]

Thanks Macro...same thing i am looking..Great..

[pra_kale]

Thanks Sunny and Antonio...for your Help...

[MarcoWedel]

glad it helped

regards

Marco