Macro to split large range of text with multiple delimiters.

Good day to you all.

I'm relatively new to the VBA scene - hence this post.

I'm looking for a way to split a string of text, in a large range, using several different delimiters. It is important to keep the delimiters as they were as they are needed for a later macro.

EXAMPLE:

My data:
Toast!Bread
Sun?Moon

After Macro (_ represents string split into 2 columns):
Toast _ Bread
Sun _ Moon

Thank you for any advice you can give me.

Hi quake,

Instead of using a macro i advice you to use Powerquery and split by delimiter.
go to tab -> data -> get data (depends on where you data is stored either a range or a file) choose that setting than search the table and or file and than instead of loading do transform.
select the column that you wanto split and go to tab Start -> look up split by column -> click logo and you have multiple options to split text based on certain criteria.

greetings

Aaron

Hi Aaron, thanks for your response.

I don't actually hae power query as i'm using 2010 Excel - and i can't download it because i'm on a work pc.
Hm.

ah damn quake,

good luck !

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

So what i'm actually trying to do is completely clean and reshuffle some data into a set format. It's complicated. I don't know how to do it all in one go so I have just been doing it in parts. Hence why i asked to split which allows me to move differnet parts around.

Hi there,

See if the following User-Defined Function in the attached workbook does what you need:

Option Explicit


Function InsertSpaces(sValue As String) As String

    Const sDELIMITERS   As String = "?!~"

    Dim iDelimiterNo    As Integer
    Dim sDelimiter      As String
    Dim sNewValue       As String

    sNewValue = sValue

    For iDelimiterNo = 1 To Len(sDELIMITERS)

        sDelimiter = Mid$(sDELIMITERS, iDelimiterNo, 1)

        sNewValue = Replace(sNewValue, sDelimiter, " ")

    Next iDelimiterNo

    InsertSpaces = sNewValue

End Function

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg,

That's great stuff thank you.

The only thing I need to be different is that the new sub strings need to be split over two cells. So if I had 'sun ! moon' the split would go to 'sun_','_moon'

The space is important as I use a LEN function for the next part of the process.

I fear i've made all of this more complicated than it needs to be. I've attached exactly what i need to do above.

Many thanks again,
Kai

Fro your attached data
=StrArrange(B9)

Function StrArrange(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "^(.+?)( .*?-\S+)?( .+)( M\d+)$"
        If .test(txt) Then txt = .Replace(txt, "$1$4$3$2")
    End With
    StrArrange = txt
End Function

My apologies.

I don't really know how to apply this to my data.

I can't even put it into a sub to be able to execute the macro - i am very new to this.

See.
Results are in E9:E13.

Woah. This is amazing, thank you so much.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Unfortunately, it doesn't exactly take care of it all. For example, the data is uploaded in a huge huge huge variety of ways. The examples i gave you were very clean and exact. These are more examples:

NUT NYLOC UNC 1/2" ST/ST A4-80
NUT ST/ST M10
BZP M20 NUT, GR 8.8
LOCKNUT ST/ST M20 KOPEX
SELF CLINCHING NUT, M12 A2-70
LOCK NUT BRASS 1 1/4" BSP
M25 LOCK NUT NICKEL PLATED BRASS

The required format stays the same.

Is this something that cannot be programmed easily as they amount of variables are huge?

Again, thank you for all your help.

Then upload a workbook with ALL the variation of data and the result that you want.

We can not read your brain.

This is the problem. I have a ridiculous amount of variations. This will take some time but I will try.

OK.

We need it anyway and please don't fail your desired result or it will be a big problem for this particular case.

Do you only have "NUT"? not "BOLT" like in your other workbook?

OK, better.

It will take time and it is already 10:30 PM here, so I will take a close look at it tomorrow...

Thank you, Jindon.

Just found irregular conversions on rows

19, 64, 66 etc,

when word HEXAGON appears, it will be out of logic.

Please take your time and make sure there is no error in conversion.
I need to compare my result with yours, and when irregular happen, I need your clear explanation.

I will go off line soon, so no need to hurry.

Apologies I did not see sooner. I was not notified of your response.

Some parts do not matter on format as their is too much variance. i.e 'HEX' can come before or after 'PLAIN' it does not matter. So for ease if you want 'HEX' to always come 2nd after 'NUT' that is fine. As long as the type 'NUT' is at the start. The size 'M20' for example is before material 'ST/ST' and then grade 'A4-80' (and in some cases finish i.e 'ZINC PLT').

So the format would be like this:

[TYPE] ('NUT') -> [ANYHTING ELSE] (i.e 'HEX', 'PLAIN', 'SELF LOCKING') -> [SIZE] -> [MATERIAL] -> [GRADE] -> [FINISH]

With regards to the irregular parts - some things are changed and not just reshuffled i.e:

HEXAGON -> HEX

But I can do this with find and replace function after any reshuffling if necessary.

Full list of find/replace are as follows:

HEXAGON -> HEX
HEAD -> HD
MACHINE -> M/C
STAINLESS/STEEL -> ST/ST
STAINLESS/ST -> ST/ST
ST/STEEL -> ST/ST
STEEL/ST -> ST/ST
S/S -> ST/ST
STEEL -> ST
NICKEL ALLOY -> NI/AL
ZINC PLATED -> ZINC PLT
',' -> ' '
COUNTERSUNK -> CSK
SOCKET -> SKT

Even if you can manage to make sure that i.e 'NUT' is always at the start and the material and then grade and then finish are at the end - that is a huge win for me.

Lots to take in, i apologise.

QUAKE2K

I have no idea about

[TYPE] ('NUT') -> [ANYHTING ELSE] (i.e 'HEX', 'PLAIN', 'SELF LOCKING') -> [SIZE] -> [MATERIAL] -> [GRADE] -> [FINISH]

What is [TYPE], [ANYTHING ELSE], [SIZE] etc from the string, so it fully depends on your result.
If the results are not correct, no way to write a code.

e.g. in row 10
data = M16 STAINLESS STEEL NYLOC NUTS (BAG OF @
your result = NUT NYLOC M16 ST/ST 25 PACK

in row 13
data = NUT PLAIN HEX
your result = NUT PLAIN HEX 7/8" UNC CL 2B THD

etc.etc...

I can not even start...

in row 13
data = NUT PLAIN HEX
your result = NUT PLAIN HEX 7/8" UNC CL 2B THD

^^^
This comes about from a long description of 'NUT PLAIN HEX'. For example: I have two cells, a short description (which has to be 40 chars or less) and a long description. Both extracted from the database - about the same 'part' i.e a type of nut. Sometimes the extract from the database will only give me 'NUT PLAIN HEX' in the short description, however in the long description it will say 'NUT PLAIN HEX 7/8" UNC CL 2B THD etc etc...'. I have to manually choose what to put in to the new short description FROM the long description as to try and give as much detail in as little as 40 chars. Taking as much info from the long desc as i can. This cannot be coded as there is no set format in the long description and there is far too much variance in information - it has to be done manually. So for the cells that i sent you that have appeared to have 'gained' information like above - this has been done manually.

This is just a roughly wrote function, so not completed (never anyway).
Test this and see how it goes.

Function StrArrange(ByVal txt As String) As String
    Static RegX As Object
    Dim i As Long, m As Object, myStr As String
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = "\b(HE(XAGON|AD)|MACHINE|NICKEL ALLOY|ZINC PLATED?|COUNTERSUNK|SOCKET)\b"
        If .test(txt) Then
            For i = .Execute(txt).Count - 1 To 0 Step -1
                Set m = .Execute(txt)(i)
                Select Case True
                    Case m Like "HEX*": myStr = "HEX"
                    Case m = "HEAD": myStr = "HD"
                    Case m Like "MA*": myStr = "M/C"
                    Case m Like "NIC*": myStr = "NI/AL"
                    Case m Like "Z*": myStr = "ZONC PLT"
                    Case m Like "C*": myStr = "CSK"
                    Case m Like "S*": myStr = "SKT"
                End Select
                txt = Application.Replace(txt, m.firstindex + 1, m.Length, myStr)
            Next
        End If
        .Pattern = "\b(S(T(AINLESS)?)?[ /])?S(T(EEL)?)?\b"
        If .test(txt) Then
            If .Execute(txt)(0).submatches(0) <> "" Then
                txt = .Replace(txt, "ST/ST")
            Else
                txt = .Replace(txt, "ST")
            End If
        End If
        .Pattern = "((^\S* *(NUT|BOLT|WASHER|SCREW))| (NUT| BOLT| WASHER| SCREW))S?\b"
        If .test(txt) Then
            StrArrange = .Execute(txt)(0).submatches(1) & .Execute(txt)(0).submatches(3): txt = .Replace(txt, "")
        End If
        .Pattern = "\bHEX\b"
        If .test(txt) Then
            StrArrange = StrArrange & " " & .Execute(txt)(0): txt = .Replace(txt, "")
        End If
        .Pattern = "\bM\d+\b"
        If .test(txt) Then
            StrArrange = StrArrange & " " & .Execute(txt)(0)
            txt = .Replace(txt, "")
        End If
        StrArrange = Application.Trim(StrArrange & " " & txt)
    End With
End Function

Unfortunately, this does not work too well. I feel the amount of variables that we are faced with here are too great for a function to work completely perfect on something like this.

One specific problem was the splitting of the sizes

Input:
BOLT ST/ST A4-80 M20 X 50MM LG

Output:
BOLT M20 ST/ST A4-80 X 50MM LG

In bold is ALL the size. They need to be together for it to make sense. What's needed is a tool that recognizes the exact format it needs to be in, programmed with a directory so it can tell which part is size and type and material etc..

As I already mentioned, I have no idea about all the things that you described.

You should explain all the things in details like how you re-arrange the string and why, otherwise just wasting time for both of us.

As i've said before there are too many variables. I would be here for days writing out all of them. Sorry for wasting your time.

If you can not explain the logic clearly, it is not possible...