• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

splitting a cell into two cells using the following delimiter and logic

saidhruv

saidhruv

Member
Dear Expert,


Request you to help in splitting a cell into two cells using the following delimiter and logic..

Cell contains Company Name as well as its Address.

We need to split the two using below mentioned delimiters.

The list of delimiters is mentioned below. Please note that LTD. is also a delimiter while LIMITED is also one.

a. LTD.

b. LIMITED

c. PVT. LTD.

d. PRIVATE LIMITED.

e. INC.

f. INCORPORATION

g. CORP.

h. CORPORATION

i. LLP.

j. Limited liability partnership

k. LLC

l. Limited liability COMPANY

m. LP.

n. Limited partnership

o. CO.

p. COMPANY

q. PLC.

r. Programmable Logic Controller

s. AG.

t. AGENCY

u. ORG.

v. organization

w. GMBH.

x. Gesellschaft mit beschränkter Haftung

y. LL.

z. Limited liability

aa. SRL.

bb. Salazar Resources Limited


The logic has to be the following:

a. In case the string has only one of the above delimiters, split the Company Name at that delimiter.

b. In case string has multiple delimiters, and they seems to be one after the other without any other word between two delimiters, split the Company Name after the right most delimiter.

c. In case string has multiple delimiters, and between two delimiters there are other words, split at the place just before we find the words or characters that are after the delimiter.

d. In case string has no delimiter, split string after the CAPS words. However, do highlight this cell so that we can verify.

e. In case some string does not have any of the above logic, pls keep the sting as it is so that it can be manually worked upon.


Sharing the example of all of the above logic in attached excel.

Note: Post delimiter there may be some special characters, i.e. could be comma or hyphen, etc.. Please include the same within Company Name itself.


Thanks!
 

Attachments

  • 2000.xlsx
    11.2 KB · Views: 7

Hi !

If there are delimiters use TextToColumns method
(or directly from Excel Data menu) …
​
 
Dear Marc - thanks! the delimiters that we have are not always in the same position. Request you to check the attachment in the initial post. The delimiters can change their sequence and also can be more than one at a time.
 
Dear All - please help for this query since the data that we have is in huge amount.. And delimiters are dynamic. Manual work is not possible to split the cell based on the aforementioned logic.

thanks!
 
@saidhruv
Could You explain ...
1) How You [B3] is like Logic#b? There are NONE of Your named 'delimiters'
... or there is almost ... y. LL. but there is NOT same text in [B3] 'LL' <> 'LL."
and (b) needs more than one match with 'delimiters' ...
so only Logic#d would match!
2) Is there almost same case with [B4] Logic#a? 'LP' <> 'LP.' and so on...
3) How many 'CAPS' letters have to find for Logic#d? one.. two..?

... if those ending "." take away from 'delimiters'
then there would be more possibilities.

>> Won't it be more clear to get those 'B-column data' ready in two columns?
>> Why let those make more extra work for next step?
 
saidhruv said:
Request you to check the attachment in the initial post
Click to expand...
Done and obviously these are not delimiters !

It could be so easy if at least a space separates last end searched
string / word from address !
From whom Dumb or Dumber data come ?‼

Better is to ask for a smart data import file.

Otherwise use InStr and Mid VBA text function, see in VBA inner help …
​
 
@Marc L ... as I tried to write ...
>> Won't it be more clear to get those 'B-column data' ready in two columns?
>> Why let those make more extra work for next step?
 
Dear vletm,


My apologies for the confusion created thru the list of “delimiters”.


Please take a note that data would have the delimiter or multiple delimiters, but they can end with any special character, i.e. delimiter could end with . or without . or it may also have , or ; or ., or ,- or any other special characters. Hence what we need to check is for only “delimiter” within the string.


Sharing the updated list of delimiters after removing the .

Also, am sharing the simplified logic for reference. The simplified logic includes all aforementioned logics except the CAPS thing. The CAPS thing can be ignored since it contains of 30% of entire data and that I will handle manually. See if You can help me out with the delimiter vba or formula..


1. LTD

2. LIMITED

3. PVT LTD

4. PRIVATE LIMITED

5. INC

6. INCORPORATION

7. CORP

8. CORPORATION

9. LLP

10. Limited liability partnership

11. LLC

12. Limited liability COMPANY

13. LP

14. Limited partnership

15. CO

16. COMPANY

17. PLC

18. Programmable Logic Controller

19. AG

20. AGENCY

21. ORG

22. organization

23. GMBH

24. Gesellschaft mit beschränkter Haftung

25. LL

26. Limited liability

27. SRL

28. Salazar Resources Limited


Logic:

Traverse from left to right within the string and search for the delimiters from the list mentioned above.

Logic1: In case string has none of the delimiters, do not split, hence column C & D would be blank.

Logic2: In case string has single delimiter or multiple delimiters that are put adjacent to each other, search for these delimiters and split at that. Please include any special characters (except “) that are appending to the delimiter till you find any other word or character.

Logic3: In case string has multiple delimiters traverse from left to right and split at the point where there are any other characters or words between two delimiters. Please include any special characters (except “) that are appending to the delimiter till you find any other word or character.


Examples:


Logic 1:


example1: TMTBiofuels6271 Florin Perkins Suite 100 Sacramento CA 95828 t:+1 916 386 8514 f:+1 916 386 8552


example2: WILLEYS FARMERS COOPERATIVE SU2040 Turner Avenue NW Grand Rapids MI 49544 t:+1 616 364 4666 f:+1 616 616 8672


Both the above strings do not have any of the delimiters, hence do not split


Logic 2:


example1: SUNOCO LOGISTICS PARTNERS LP37a Teed Drive Randolph MA 02368 t:+1 781 961

The above string has to split at LP


example2: SUNOCO LOGISTICS PARTNERS LPoeaja la, 6165, tiyara rd, 50

The above string has to split at LP


example3: TA Operating Corporation,-TravelCenters of America"901 E. Byrd St.Richmond, VA 23219Washington"

The above string has to split at Corporation,-


example4: TRAFIGURA AG"1099 14th Street, N.W., Suite 350Washington, DC 20005"

The above string has to split at AG cause we have to ignore double quotes as special character since it will have to move to Address..


example5: WAVAHO OIL CO., INC."Lincoln Ave, Salinas, CA 93901 (831) 758-7321 Non-Emer.(831) 758-7090 "

The above string has to split at WAVAHO OIL CO., INC.


example6: SCHERING AGAlfredo Pablo Moro, Rodo 6424 (1440) Capital Federal, Argentina

The above string has to split at SCHERING AG


example7: OY STAR ABCaspian Tamin Pharmaceutical CompanyDarouGostar Blog., Beastoon Ave., Dr.Fatemi Sq., Tehran, Iran

The above string has to split at OY STAR ABCaspian Tamin Pharmaceutical Company


Logic 3:


example1: STUYVESANT FUEL TERMINAL CO LL601 Campus Drive New Brighton MN 55112 t:+1 651 633 3100 f:+1 651 633 3101

The above string has to split at CO LL


example2: SWIFTY OIL CO INCP. Box. 25924 Crocker Rd

The above string has to split at CO INC


example3: SYLVITE INVESTMENTS (USA) INC DBA SYL CO SERV35722 Bennet Str, River Villa. (9)

The above string has to split at SYLVITE INVESTMENTS (USA) INC


example4: TAZINK CO DBA SOUTHWEST FUELS INC"426 Work St, Salinas, CA 93901 (831) 758-7233 (831) 758-7940 "

The above string has to split at TAZINK CO


example4: THE SHELL COMPANY (PR) LIMITED5855-C Oakbrook Parkway Norcross GA 30093 t:+1 800 241 5000 f:+1 770 453 9436

The above string has to split at THE SHELL COMPANY


example5: TECHNOCHEMIE GMBHCPL Inc., - Bulk Pharmaceuticals & Intermediates, 16020 Swingley Ridge Rd. St. Louis, MO USA

The above string has to split at TECHNOCHEMIE GMBH


example6: INDENA GRUPPO INVERNI DELLA BEFFAGranard Pharmaceutical Sales & Marketing, LLC, 1500 Meeting House Road Sea Girt, New Jersey 08750

The above string has to split at INDENA GRUPPO INVERNI DELLA BEFFAG


Thanks for your patience!
 

Attachments

  • ExampleSplitDelimiter.xlsx
    10.1 KB · Views: 4
Yes!
case 'CO' and example2:
WILLEYS FARMERS COOPERATIVE SU2040 Turner Avenue NW Grand Rapids MI 49544 t:+1 616 364 4666 f:+1 616 616 8672
Isn't there?
case 'LP' and example1:
SUNOCO LOGISTICS PARTNERS LP37a Teed Drive Randolph MA 02368 t:+1 781 961
You wrote that Please take a note that data would have the delimiter or multiple delimiters, but they can end with any special character, i.e. delimiter could end with . or without . or it may also have , or ; or ., or ,- or any other special characters.
Which one is 3? ... and so on ... there are many same kind of cases!
Interesting case, but needs less possibilities!
>> As I already wrote twice:
>> Won't it be more clear to get those 'B-column data' ready in two columns?
>> Why let those make more extra work for next step?
 
dear vletm - my mistake.. in example2 it should end at CO.. and 3 is an integer so it has to be excluded.. so no alphabet or integer..
See if you can help me with the formula..

btw can you please elaborate on ready part in column B?
 
As I yet advised, try InStr and Mid VBA text function, see in VBA inner help …

As I wrote at least a space must separate last word in name
and the beginning of address !

And a parsec far better is to just insert a real delimiter between
like a comma, a tabulation, a pipe or whatever the specific character !
In this case it will not ever need a code, or just a single codeline ‼

Where these data come from ? Just ask to source of them …
​
 
dear vletm - the example given in excel was made by me hence you could find the mistake in CO.. however, if you can make logic it would be the best thing for me cause it will reduce my manual efforts by more than 80%..
 
dear Marc - i am already using tilde ~ as a delimiter however am doing it manually and then with left formula am differentiating string before and after ~.. but since it totally manual, hence requesting for a formula..
 

As I wrote with such a delimiter you ever dot no need any formula
neither a code as Excel from Data menu has a Convert
or Text to columns function !

And by code it needs a single codeline via TextToColumns method !
To see in VBA inner help like in threads of this forum …
​
 
@saidhruv
it will need to something like this ... but
'LP', 'CO', 'AG', 'ORG', 'LL' ... plus many more - NOT GOOD AT ALL!
... do not ask one question!
Press [Try To Cut] ... and be active!

>> As I already wrote three times:
>> Won't it be more clear to get those 'B-column data' ready in two columns?
>> Why let those make more extra work for next step?
 

Attachments

  • 2000.xlsb
    28.3 KB · Views: 3
vletm said:
@saidhruv
it will need to something like this ... but
'LP', 'CO', 'AG', 'ORG', 'LL' ... plus many more - NOT GOOD AT ALL!
... do not ask one question!
Press [Try To Cut] ... and be active!

>> As I already wrote three times:
>> Won't it be more clear to get those 'B-column data' ready in two columns?
>> Why let those make more extra work for next step?
Click to expand...
thanks dear vletm !
 
For the data provided.
Code: 
Sub test()
    Dim a, i As Long
    With [a2].CurrentRegion.Resize(, 4)
        .Offset(1, 2).Resize(.Rows.Count - 1, .Columns.Count - 2).ClearContents
        a = .Value
        With CreateObject("VBScript.RegExp")
            .IgnoreCase = True
            .Pattern = "^(.+?(AG|LP|INC| Co LL|Co(rporation|mpany)[,-]*| Co(\.,)? (INC)?|GMBH))(""?.+)$"
            For i = 2 To UBound(a, 1)
                If .test(a(i, 2)) Then
                    a(i, 3) = Trim$(.Replace(a(i, 2), "$1"))
                    a(i, 4) = Trim$(.Replace(a(i, 2), "$6"))
                End If
            Next
        End With
        .Value = a
    End With
End Sub
 

Attachments

  • ExampleSplitDelimiter with code.xlsm
    18.5 KB · Views: 6
jindon said:
For the data provided.
Code: 
Sub test()
    Dim a, i As Long
    With [a2].CurrentRegion.Resize(, 4)
        .Offset(1, 2).Resize(.Rows.Count - 1, .Columns.Count - 2).ClearContents
        a = .Value
        With CreateObject("VBScript.RegExp")
            .IgnoreCase = True
            .Pattern = "^(.+?(AG|LP|INC| Co LL|Co(rporation|mpany)[,-]*| Co(\.,)? (INC)?|GMBH))(""?.+)$"
            For i = 2 To UBound(a, 1)
                If .test(a(i, 2)) Then
                    a(i, 3) = Trim$(.Replace(a(i, 2), "$1"))
                    a(i, 4) = Trim$(.Replace(a(i, 2), "$6"))
                End If
            Next
        End With
        .Value = a
    End With
End Sub
Click to expand...

dear jindon - many many many thanks for your kind help. this has more than served the purpose. now my 80% manual efforts have diminished.. thanks again.. God Bless You with best of health and happiness at all times..
 
You must log in or register to reply here.
Back
Top