Delimiting by 2 different characters?

[sunnygilluk]

Hi all,

Im trying to format some data in excel that looks like this in A2:

05/06 00:00:00 AZT4512SD AA 000000 (ABC DEF) ZZ123456 1.1.1 0000 123 (ABC) A 1234 123456

from 1 cell into 1 for each "field" of data seperately (B2 = 05/06 C2 =00:00:00 etc)

Now normally i would delimit by space - however another record may also look like this:

05/06 00:00:00 AZT4512SD AA 000000 (ABC DEF GHI JKL) ZZ123456 1.1.1 0000 123 (ABC DEF) A 1234 123456

As you can see there are now more spaces in the items within the brackets.

I have found formulas to extract each piece of data (some fields have variable number of characters) by searching for text within spaces on first / second / third etc occurence and also within first / second brackets. However the formulas im using for characters between spaces go wrong once there are spaces within the brackets (after the brackets they become askew). Is there a way to do this and ignore what is in the brackets?

Thanks

 

[Scott Huish]

What exactly are you looking to return? Your first example has a space in the value in parentheses as well (the first set). How does this get treated differently than the second example?

 

[Dr. Demento]

sunnygilluk - Welcome to the board!!

My first thought would be to identify all the strings between brackets and replace the space with a placeholder (~, % - some term that won't be found in the string). Perform the text to columns and then replace all the placeholders with a space.

Unfortunately, how to identify all the substrings between the brackets and replace the space with a placeholder only for those instances is beyond my current under-developed superpowers. The Google??

 

[FDibbins]

With your data sample in A2...
A2=05/06 00:00:00 AZT4512SD AA 000000 (ABC DEF) ZZ123456 1.1.1 0000 123 (ABC) A 1234 123456

[Table="width:, class:grid"][tr][td] [/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][/tr]
[tr][td]

2​

[/td][td]05/06[/td][td]00:00:00[/td][td]AZT4512SD[/td][td]AA[/td][td]000000[/td][td](ABC[/td][td]DEF)[/td][td]ZZ123456[/td][td]1.1.1[/td][td]0000[/td][td]123[/td][td](ABC)[/td][td]A[/td][td]1234[/td][td]123456[/td][/tr]
[/table]
B2=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),LEN($A2)*(COLUMN()-2)+1,LEN($A2)))
copied across as needed

 

[sunnygilluk]

With your data sample in A2...
A2=05/06 00:00:00 AZT4512SD AA 000000 (ABC DEF) ZZ123456 1.1.1 0000 123 (ABC) A 1234 123456

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[TD]

P​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]05/06[/TD]
[TD]00:00:00[/TD]
[TD]AZT4512SD[/TD]
[TD]AA[/TD]
[TD]000000[/TD]
[TD](ABC[/TD]
[TD]DEF)[/TD]
[TD]ZZ123456[/TD]
[TD]1.1.1[/TD]
[TD]0000[/TD]
[TD]123[/TD]
[TD](ABC)[/TD]
[TD]A[/TD]
[TD]1234[/TD]
[TD]123456[/TD]
[/TR]
</tbody>[/TABLE]

B2=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),LEN($A2)*(COLUMN()-2)+1,LEN($A2)))
copied across as needed

Thanks very much for your responses - the above is not quite what im after, anything in brackets i would need to have in 1 cell, not seperated, the rest is all OK. So in the above example I would need G and H in 1 cell, and if there were 2 spaces within those brackets i would need G H and I in 1 cell (as the data in those brackets will have gone across 3 cells).

Thanks in advance!

 

[István Hirsch]

How many brackets can be (maximum) in a cell?

 

[pgc01]

Hi

For any number of parentheses with any number of items inside a udf solution would be easier.

Would you want to use a udf or you need a formula solution?

 

[sunnygilluk]

How many brackets can be (maximum) in a cell?

After conversion i would only need the open and closed bracket in the cell with the data in between, or i dont mind losing the brackets, or the spaces between the words in the brackets.

in A2 there will only ever be 2 items in brackets, but the spaces between may vary.

 

[sunnygilluk]

I would really prefer a formula solution if possible. There will only ever be 2 sets of parentheses in A2, and each one will have no more than 10 spaces inside.

 

[István Hirsch]

Give this a try (cells in column "A" will change!):

Sub Ttocol()
Dim c As Range
 Application.DisplayAlerts = False
 With CreateObject("VBScript.regexp")
    For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    For i = 1 To 2
     .Pattern = ".*([(].+?[)]).*"
    If .test(c) Then x = .Replace(c, "$1")
    y = Replace(Replace(Replace(x, " ", "-"), "(", ""), ")", "")
    c = Replace(c, x, y)
    Next
    Next
    Range("A1", Cells(Rows.Count, "A").End(xlUp)).Select
    Selection.TextToColumns Destination:=Range("b1"), Space:=True
    Application.DisplayAlerts = True
  End With
End Sub