Sep 14 2021 02:36 PM
Hi there,
If there some sort of formula or command that will allow me to strip one part of a field and move it to another one. What I mean is we have a music index program that can export to CSV but the index number is exported with the title.
So we could have ... KINGSTON TOWN [MCA12345] in A1 but I would like it to have KINGSTON TOWN in A1 and MCA12345 in A3 (without the brackets) and do this for the following 60,000 records.
Here is my level of experience. Used Microsoft Works for many many years. Only just got the Office 365 2 days ago and have NO NO NO NO NO Clue how to do things like this so if you know, please be gentle and treat me like I am really thick as I am when using office.
See https://prnt.sc/1s7oxw9 for a screen shot of what we have.
Thanks in advance to all who take time to read this. If I had hair I would be pulling it out.
Sep 14 2021 03:05 PM
There are two text manipulation functions that do exactly what you want to do.
First, I entered into cell A1 the following
Baby Come Back [PIO183811]
Then in cell B1 the following formula
=LEFT(A1,FIND("[",A1)-2)
This just says get the left most characters of cell A1, to a length equal to two less than where you find the character "["
And in cll C1 the following formula
=MID(A1,FIND("[",A1)+1,LEN(A1)-FIND("[",A1)-1)
this one is more complicated, but it takes text from the "middle" of A1--it would have been easier if you didn't want to strip the square brackets--so there are calculations mostly to figure out where to start and then how long.....both of which play off the position of the opening bracket.
A spreadsheet is attached
Sep 14 2021 03:16 PM
Sep 14 2021 03:38 PM
I"m a little confused on what you're saying. Did you open my spreadsheet to see the results it delivers? The reason I sent you what I did was with the expectation that you or somebody else with a tad more knowledge might be able to just use those formulas as a starting point.
In any event, it doesn't look like your spreadsheet contains anything proprietary or confidential: can you just post the whole spreadsheet so I or somebody else can enter the formulas.
Sep 14 2021 03:42 PM
Sep 14 2021 03:44 PM
Sep 14 2021 03:47 PM
Sep 15 2021 05:43 AM