| 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
|
how to split data into columns between [ ] when there can be more than
- Thread starter karynd
- Start date
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)&"]"),"")
=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)&"]"),"")
Peter Bartholomew
Well-Known Member
Using 365 insider beta
would return the 4 fields.
For older versions maybe someone can help with FILTERXML.
Code:
= TEXTBEFORE(TEXTAFTER(item,"[",{1,2,3,4}),"]")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
Peter Bartholomew
Well-Known Member
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
Gargamelbarbosa
New Member
Hi , another alternative * 365 insider *
= TEXTBEFORE(TEXTAFTER(item,"[",sequence(,4)),"]")
= 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
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
You are welcome and wish you all the best on your healing journey sending.
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: