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

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

2​

05/06

00:00:00

AZT4512SD

AA

000000

(ABC

DEF)

ZZ123456

1.1.1

0000

123

(ABC)

A

1234

123456

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

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

2​

05/06

00:00:00

AZT4512SD

AA

000000

(ABC

DEF)

ZZ123456

1.1.1

0000

123

(ABC)

A

1234

123456

<tbody>
</tbody>

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