Removing Data from one field and moving to another field

%3CLINGO-SUB%20id%3D%22lingo-sub-2750783%22%20slang%3D%22en-US%22%3ERemoving%20Data%20from%20one%20field%20and%20moving%20to%20another%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2750783%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20some%20sort%20of%20formula%20or%20command%20that%20will%20allow%20me%20to%20strip%20one%20part%20of%20a%20field%20and%20move%20it%20to%20another%20one.%26nbsp%3B%20What%20I%20mean%20is%20we%20have%20a%20music%20index%20program%20that%20can%20export%20to%20CSV%20but%20the%20index%20number%20is%20exported%20with%20the%20title.%3C%2FP%3E%3CP%3ESo%20we%20could%20have%20...%20KINGSTON%20TOWN%20%5BMCA12345%5D%20in%20A1%20but%20I%20would%20like%20it%20to%20have%20KINGSTON%20TOWN%20in%20A1%20and%20MCA12345%20in%20A3%20(without%20the%20brackets)%20and%20do%20this%20for%20the%20following%2060%2C000%20records.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20my%20level%20of%20experience.%26nbsp%3B%20Used%20Microsoft%20Works%20for%20many%20many%20years.%26nbsp%3B%20Only%20just%20got%20the%20Office%20365%202%20days%20ago%20and%20have%20NO%20NO%20NO%20NO%20NO%20Clue%20how%20to%20do%20things%20like%20this%20so%20if%20you%20know%2C%20please%20be%20gentle%20and%20treat%20me%20like%20I%20am%20really%20thick%20as%20I%20am%20when%20using%20office.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fprnt.sc%2F1s7oxw9%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fprnt.sc%2F1s7oxw9%3C%2FA%3E%26nbsp%3Bfor%20a%20screen%20shot%20of%20what%20we%20have.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20to%20all%20who%20take%20time%20to%20read%20this.%26nbsp%3B%20If%20I%20had%20hair%20I%20would%20be%20pulling%20it%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2750783%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2750827%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20Data%20from%20one%20field%20and%20moving%20to%20another%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2750827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1155978%22%20target%3D%22_blank%22%3E%40Foley1972%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20two%20text%20manipulation%20functions%20that%20do%20exactly%20what%20you%20want%20to%20do.%3C%2FP%3E%3CP%3EFirst%2C%20I%20entered%20into%20cell%20A1%20the%20following%3C%2FP%3E%3CP%3EBaby%20Come%20Back%20%5BPIO183811%5D%3C%2FP%3E%3CP%3EThen%20in%20cell%20B1%20the%20following%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLEFT(A1%2CFIND(%22%5B%22%2CA1)-2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThis%20just%20says%20get%20the%20left%20most%20characters%20of%20cell%20A1%2C%20to%20a%20length%20equal%20to%20two%20less%20than%20where%20you%20find%20the%20character%20%22%5B%22%3C%2FP%3E%3CP%3EAnd%20in%20cll%20C1%20the%20following%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMID(A1%2CFIND(%22%5B%22%2CA1)%2B1%2CLEN(A1)-FIND(%22%5B%22%2CA1)-1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ethis%20one%20is%20more%20complicated%2C%20but%20it%20takes%20text%20from%20the%20%22middle%22%20of%20A1--it%20would%20have%20been%20easier%20if%20you%20didn't%20want%20to%20strip%20the%20square%20brackets--so%20there%20are%20calculations%20mostly%20to%20figure%20out%20where%20to%20start%20and%20then%20how%20long.....both%20of%20which%20play%20off%20the%20position%20of%20the%20opening%20bracket.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20spreadsheet%20is%20attached%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2750858%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20Data%20from%20one%20field%20and%20moving%20to%20another%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2750858%22%20slang%3D%22en-US%22%3EI%20only%20want%20to%20remove%20it%20from%20the%20first%20Column%20and%20then%20move%20it%20to%20a%20new%20column.%20I%20would%20prefer%20not%20to%20have%20the%20brackets%20in%20the%20first%20column%20but%20if%20it%20is%20difficult%20to%20remove%20them%20in%20the%20new%20column%20then%20that%20is%20ok.%20Unfortunatly%20I%20have%20to%20say%20that%20I%20am%20pretty%20confused%20on%20that%20as%20I%20have%20never%20ever%20made%20a%20formula%20before%20so%20would%20not%20know%20where%20to%20start.%20The%20code%20is%20ALWAYS%20the%20same%20length.%20Three%20letters%2C%20and%206%20numbers.%20When%20I%20said%20treat%20me%20like%20I%20am%20thick%2C%20MORE%20THICK%20then%20that%20ha%20ha.%20Sorry%20I%20am%20a%20novice%20on%20this.%20Thanks%20for%20the%20message%20back.%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@Foley1972 

 

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.

@Foley1972 

 

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. 

I will try to open again.. When I opened it I got a picture on the screen so maybe I did it wrong.... Will try again
Thanks
I now have it showing. Thanks. Wierd thing was that when I typed the first few codes it appears that Excel did the rest for me. I just now want to see if I can delete the text from the first field. The would be great then.
Ok I looked at that works great to copy the code over. I am guessing it is not so easy to be able to remove it from the first field so rather then copy paste it is cut paste. A good start though
Thanks
You can copy from the column where you ran the formula that made the text "clean" and then do a "Paste ....Special" (from the menu at the top of the screen) and select "Values" to paste it on top of Column A.

However, do it with just the first cell the first time to make sure you're doing it right.