• 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.

how to split data into columns between [ ] when there can be more than

karynd

New Member
AUSP Policy ID: 320764 Eff: 29/09/2021 Martin Ayles 63 Pty Ltd

[10 Jan 2022] [Daniel Joiner WA]
Status changed to Complete [11 Jan 2022] [Jo Atwell]

am trying to split the above date and I want the date and the name in a new column - the data could exist of more than just 2 dates and names it could be 20 or 10 or just 1 but I just want to be able to split out based on any data in between the [ ] into its own seperate column as per below

is there a formula that would work nicely for this ?
appreciate your help

cheers
karyn


80878

 
A] Try this formula in B1, if you are Office 365 or Excel 2021 user:

=TRANSPOSE(FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(A1,"[","<b>"),"]","</b>")&"</a>","//b"))

or

B] Try this copy across formula in B1, if you are Excel 2019 or below user :

=IFERROR(FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE($A1,"[","<b>"),"]","</b>")&"</a>","//b["&COLUMN(A1)&"]"),"")

80880
 
Using 365 insider beta
Code:
= TEXTBEFORE(TEXTAFTER(item,"[",{1,2,3,4}),"]")
would return the 4 fields.
For older versions maybe someone can help with FILTERXML.
 
Using 365 insider beta
Code:
= TEXTBEFORE(TEXTAFTER(item,"[",{1,2,3,4}),"]")
would return the 4 fields.
For older versions maybe someone can help with FILTERXML.

It is nice to have another solution for Office 365 Insider Beta user

I have a new Excel 2021 by 2 days ago, although it only contents half of the Office 365 functions, anyway I can solve half of the Office 365 questions.

But sadly, I suffered 2 strokes in June of this year, I am still within the "high risk period". My body condition still weak and unable to spend more time in doing the "New function study"..........

bosco_yip
 
bosco_yip Good morning.

I hope you're overcoming all this, I'm together thinking positive for your progress and recovery, now you can notice that in this 2021 version no need to type anymore CTRL+SHIFT+ENTER anymore.

A big hugs

Decio
 
bosco_yip Good morning.

I hope you're overcoming all this, I'm together thinking positive for your progress and recovery, now you can notice that in this 2021 version no need to type anymore CTRL+SHIFT+ENTER anymore.

A big hugs

Decio
Hi deciog,

Yes, I notice that the new Excel2021 need not Ctrl+Shift+Enter, need not to use Iferror, and also formula need not to copy across right

Regards
 
Hi , another alternative * 365 insider *

= TEXTBEFORE(TEXTAFTER(item,"[",sequence(,4)),"]")
@Gargamelbarbosa ,

Please be noted that, your formula is not a good alternative

- Peter's formula : =TEXTBEFORE(TEXTAFTER(item,"[",{1,2,3,4}),"]")

- Gargamelbarbosa's formula : =TEXTBEFORE(TEXTAFTER(item,"[",sequence(,4)),"]")

1] They look similar, but your formula have one more function "sequence"

2] Remember : "In Excel, more function will cause lower speed and spend more memory"

3] So, Peter's formula is better

Regards
 
A] Try this formula in B1, if you are Office 365 or Excel 2021 user:

=TRANSPOSE(FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(A1,"[","<b>"),"]","</b>")&"</a>","//b"))

or

B] Try this copy across formula in B1, if you are Excel 2019 or below user :

=IFERROR(FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE($A1,"[","<b>"),"]","</b>")&"</a>","//b["&COLUMN(A1)&"]"),"")

View attachment 80880

Bosco_Yip Thankyou so much the a) worked a treat - you are a legend !!!!! I copied it down to the next that had 6 dates and it still worked.

I am so sorry to hear about you health. Thankyou for taking the time out to answer this and Sending you lots of healing energy.
I went through a cancer journey in June last year and still on a rehab journey after they removed neck muscles to get to the cancer only just going back to my accounting job part time this June.
Not sure if this is allowed but If It is something you are open to I could email you a relaxing hypnosis recording to assist your body to heal itself. I know accounting and hypnosis is a weird combination but I have studied many healing modalities too and if it something you are open to I could create you something personalised as a thankyou for solving my problem. Not sure but I imagine I could attach it here as a file otherwise just need an email ?
Totally understand if it is not something you are interested but I wanted to offer as I find it helps me.
 
Bosco_Yip Thankyou so much the a) worked a treat - you are a legend !!!!! I copied it down to the next that had 6 dates and it still worked.

I am so sorry to hear about you health. Thankyou for taking the time out to answer this and Sending you lots of healing energy.
I went through a cancer journey in June last year and still on a rehab journey after they removed neck muscles to get to the cancer only just going back to my accounting job part time this June.
Not sure if this is allowed but If It is something you are open to I could email you a relaxing hypnosis recording to assist your body to heal itself. I know accounting and hypnosis is a weird combination but I have studied many healing modalities too and if it something you are open to I could create you something personalised as a thankyou for solving my problem. Not sure but I imagine I could attach it here as a file otherwise just need an email ?
Totally understand if it is not something you are interested but I wanted to offer as I find it helps me.
@karynd

Thank you so much of your kindness

Unfortunately, I lost my hearing around 95%, I am not able to hear your "relaxing hypnosis recording"

Anyway, I still need say one more word of "Thank you very much"

bosco_yip
 
@karynd

Thank you so much of your kindness

Unfortunately, I lost my hearing around 95%, I am not able to hear your "relaxing hypnosis recording"

Anyway, I still need say one more word of "Thank you very much"

bosco_yip

You are welcome and wish you all the best on your healing journey sending.
 
@karynd

Thank you so much of your kindness

Unfortunately, I lost my hearing around 95%, I am not able to hear your "relaxing hypnosis recording"

Anyway, I still need say one more word of "Thank you very much"

bosco_yip

so sorry about your hearing.

I have a question about my original question - I was asking the report to replace some data with the bracket thinking it would work but they are now saying they can if the data was like this do you have an option to get the same info - i tried replacing your "[" with "___" but it is like it does not read that - I am not sure if it even 3 underscores. I tried copying it but it wont work. I am going to be chatting to the report writer from the system surely they can change it to a bracket but I thought since you were so brilliant you might have a solution if the data looked like this
again I am wanting the date in one column and the name in the next column in every circumstance

chees


AUSP Policy ID: 334980 Eff: 20/04/2022 PNM WA PTY LTD
Assigned user changed to Joan Lim
___06 Jul 2022 - Joan Lim___
Status changed to In Progress. 6/7/2022 JL spoke to Beau and he advised client paid them 27/4 and they remitted payment to us on 16/6. He will ask his accounts to send us the remittance advice.
___06 Jul 2022 - Joan Lim___
7/7 Mei emailed broker re premium discrepancy - waiting to hear from them.
___11 Jul 2022 - Joan Lim___
29/7 - emailed reminder to Beau for revised closing
___29 Jul 2022 - Joan Lim___
Assigned user changed to Neha Upadhyay. Neha please allocate the premium sitting in UA cash ID 114780. The client has taken back the $45.98 overpayment.
___10 Aug 2022 - Joan Lim___

___12 Aug 2022 - Nancy Mattos___
 
so sorry about your hearing.

I have a question about my original question - I was asking the report to replace some data with the bracket thinking it would work but they are now saying they can if the data was like this do you have an option to get the same info - i tried replacing your "[" with "___" but it is like it does not read that - I am not sure if it even 3 underscores. I tried copying it but it wont work. I am going to be chatting to the report writer from the system surely they can change it to a bracket but I thought since you were so brilliant you might have a solution if the data looked like this
again I am wanting the date in one column and the name in the next column in every circumstance

chees


AUSP Policy ID: 334980 Eff: 20/04/2022 PNM WA PTY LTD
Assigned user changed to Joan Lim
___06 Jul 2022 - Joan Lim___
Status changed to In Progress. 6/7/2022 JL spoke to Beau and he advised client paid them 27/4 and they remitted payment to us on 16/6. He will ask his accounts to send us the remittance advice.
___06 Jul 2022 - Joan Lim___
7/7 Mei emailed broker re premium discrepancy - waiting to hear from them.
___11 Jul 2022 - Joan Lim___
29/7 - emailed reminder to Beau for revised closing
___29 Jul 2022 - Joan Lim___
Assigned user changed to Neha Upadhyay. Neha please allocate the premium sitting in UA cash ID 114780. The client has taken back the $45.98 overpayment.
___10 Aug 2022 - Joan Lim___

___12 Aug 2022 - Nancy Mattos___
This is a different data structure in compare with your post#1 data and will use another formula.

In refer to the forum rule, " one post one question"

You are requested to open a new thread for your new question and give your expected result
Thank you

bosco_yip
 
Last edited:
Back
Top