Forum Discussion
Removing Data from one field and moving to another field
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.
7 Replies
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
- I only want to remove it from the first Column and then move it to a new column. I would prefer not to have the brackets in the first column but if it is difficult to remove them in the new column then that is ok. Unfortunatly I have to say that I am pretty confused on that as I have never ever made a formula before so would not know where to start. The code is ALWAYS the same length. Three letters, and 6 numbers. When I said treat me like I am thick, MORE THICK then that ha ha. Sorry I am a novice on this. Thanks for the message back.
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.
