# Solved Macro for combining partial text in cells

April 6, 2012 at 15:38:00
Specs: Windows XP
 I was wondering if someone can help me with a Macro. I need an output of a combination of text in a cell. I can do this manually by copyng and pasting, but figured if there is an easier way to do this since I have so much data I have to sort through. This is what I am looking for:For Example:Cell A1 has Test/ChrisCell A2 has Test/CharlesCell A3 has Procedure/TomCell A4 has Procedure/SteveCell B1 has MicrosoftCell B2 has MicrosoftCell B3 has ExcelCell B4 has ExcelOutput:Cell C1: Chris MicrosoftCell C2: Charles MicrosoftCell C3: Tom ExcelCell C4: Steve ExcelSo when you run the Macro, it combines Column A (partial) with the text in Column B and generates the output in Column C. I hope that makes sense. Any help in greatly appreciated. Thank you in advance.

See More: Macro for combining partial text in cells

April 11, 2012 at 08:51:57
 Why can't you just insert a new column in front of column A:So instead of this:``` A B 1) Test/Chris Microsoft 2) Test/Charles Microsoft 3) Procedure/Tom Excel 4) Procedure/Steve Excel ```You have your people input the data like this:``` A B C 1) Test Chris Microsoft 2) Test Charles Microsoft 3) Procedure Tom Excel 4) Procedure Steve Excel ```Then you can use some type of Data Validationto make sure everything is entered in a consistent manner.MIKEhttp://www.skeptic.com/

#1
April 6, 2012 at 16:00:34
 How about a using a formula:In Cell C1 enter:=RIGHT(A1,LEN(A1)-FIND("/",A1,1))&" "&B1Drag down as many rows as neededMIKE

Report •

#2
April 8, 2012 at 10:23:58
 Thanks for the reply Mike.There are a couple reasons why I am asking for a Macro versus a formula. Although a formula would work for the most part...there are cases where a Marco will be better. In the formula you have a -FIND("/"...in the the formula. There are cases where "/" won't be the indicated to be replaced. There are instances where it's a string of words, that would need to be searched and replaced. "/" just happens to be used the most in my data...With a Macro I can designate what to look for and what to replace with every instance. Also when copying the Cell, in this case C1, it'll copy the formula. There are cases where I need to copy and paste the information into a different spreadsheet or word document. So when I copy the information in the cell...it'll copy the formula and not the details in the cell.

Report •

#3
April 8, 2012 at 13:18:23
 re" "Also when copying the Cell, in this case C1, it'll copy the formula."You are aware of Copy...PasteSpecial...Values aren't you?By using that technique, you can paste the result of the formula, not the formula itself.re: "With a Macro I can designate what to look for and what to replace with every instance. "You can do the same thing via a simple modification to Mike's formula.Put a / in D1 and use this formula to get the same results:=RIGHT(A1,LEN(A1)-FIND(\$D\$1,A1,1))&" "&B1Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
April 10, 2012 at 10:16:20
 Yes I'm aware of copy..paste special "value"...but it's not always being copied into another Excel sheet. Soemtimes it's copied into a notepad or other word document that doesn't have the option for "Paste Special"I tried the formula and getting a #Value! error. Also as indicated before the "/" isn't always going to be the indicator...or at times there are more then just 1 "/" in the string. For example:Cell A1 has Test/Today/ChrisCell A2 has Test//Tomorrow/CharlesCell A3 has Procedure/TomCell A4 has Procedure/SteveCell B1 has MicrosoftCell B2 has MicrosoftCell B3 has ExcelCell B4 has ExcelOutput:Cell C1: Chris MicrosoftCell C2: Charles MicrosoftCell C3: Tom ExcelCell C4: Steve ExcelI just need to get rid of everything in Column A up to a certain point and add it before the information in Column B and have the output in Column C. Thanks again for all your help.

Report •

#5
April 10, 2012 at 12:33:45
 First, a posting tip:Your original post says nothing about a changing indicator or that more than one indicator may exist. Since there is no way we could have known that, we end up wasting time (yours, as well as ours) suggesting solutions that won't fit your needs.When using a help forum such as this, you should try to be as complete as possible with your requirements in your first post so that those that are trying to help are working on the proper issue.That said...Based on this example data:Test/Today/ChrisTest//Tomorrow/CharlesProcedure/TomProcedure/Steve...the one consistant thing I see is that the string you are stripping off has 2 things in common:1 - It appears after the last indicator2 - It starts with an Upper Case character.If that will always be the case, then this code will strip off Chris, Charles, etc.As written it works only for A1. If it does indeed do what you want, let me know and I'll add the extra code to loop through a range of cells.Note: The code does not care what indicator you are using or how many times it appears. It is simply looking for the last Upper Case character in the string and returning that character and everything after it.```Sub LastSring() 'This code will search A1, starting with the last character and move backwards 'until it find an Upper Case character. 'It will increment a counter each time it doesn't find an Upper Case character. 'Once it finds the Upper Case character, it will use the Right function and the 'counter to produce a string that starts with the Upper Case character. 'Search backwards through A1 For c = Len(Range("A1")) To 1 Step -1 'Increment counter if Uppercase not found If UCase(Mid(Range("A1"), c, 1)) <> Mid(Range("A1"), c, 1) Then numChrs = numChrs + 1 Else 'Increment count one last time to grab the Uppercase character numChrs = numChrs + 1 'Stop searching Exit For End If Next 'Produce string myString = Right(Range("A1"), numChrs) MsgBox myString End Sub```Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#6
April 10, 2012 at 15:13:47
 Sorry DerbyDad03...the last thing I want to do is waste your time. You have been sooo wonderful and helpful not only in this post but others as well. I will take your tip from here on out. I really do appreciate all your time and effort. The Macro you wrote..looks like it will do the trick for the most part. The problem that I am having and not anything to do with your macro, are the inconsistencies in the spreadsheet. With multiple people update it, everyone has a different way of typing and doing things. I don't expect you to account for that. I'm in the process of trying to standardize this. With that being said...so I'm not sure if searching for the last upper case will do just tthe trick. Is there a way to look/search for a string of words or characters versus upper case? For example if you use the word "family"...I can insert a column before and just add Family and combine the two columns so it will look like familyChris or familychris...then we can use if from the word family....not sure if that would work or not...But seriously thank you for all of your help...it's much appreciated!

Report •

#7
April 10, 2012 at 17:33:28
 You've lost me here.If you start with Test/Today/Chris and then "insert a column before and just add Family and combine the two columns so it will look like familyChris or familychris" then you must already have a way to have extracted Chris.How else would you end up with familyChris unless you extract Chris fromTest/Today/Chris? Isn't that the original goal...to extract Chris from Test/Today/Chris?It sounds like you are going in circles.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#8
April 11, 2012 at 08:51:57
 Why can't you just insert a new column in front of column A:So instead of this:``` A B 1) Test/Chris Microsoft 2) Test/Charles Microsoft 3) Procedure/Tom Excel 4) Procedure/Steve Excel ```You have your people input the data like this:``` A B C 1) Test Chris Microsoft 2) Test Charles Microsoft 3) Procedure Tom Excel 4) Procedure Steve Excel ```Then you can use some type of Data Validationto make sure everything is entered in a consistent manner.MIKEhttp://www.skeptic.com/

Report •

#9
April 11, 2012 at 09:14:41
 Try this and see if it works, it did for the new examples you posted:=RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"/","#",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))&" "&B1MIKEhttp://www.skeptic.com/

Report •

#10
April 11, 2012 at 09:54:32
 He has said a few times that the "indicator" won't always be a slash (/) which is why I offered a formula where the indicator could be put in a referenced cell.I haven't tested your latest suggestion, but I assume it work the same way, i.e. use a cell reference instead of the hard coded slash.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#11
April 11, 2012 at 10:06:37
 DerbyDad03, yes it works just as well with a cell reference.MIKEhttp://www.skeptic.com/

Report •

#12
April 12, 2012 at 18:14:54
 With your guys' help, I was able to figure it out. What I ended up doing was doing a text to column delmited by all factors...it actually gave me a bunch of columns...then I inserted a column with one specifc indicator. This made it easier and I was able to manager. Used the formulas you guys supplied and was able to get the output that I wanted. Although the Macro that DerbyDad03 posted, actually gave me a new idea to try with my data. Once again...thank you all for your help!

Report •