Solved Macro for combining partial text in cells

Microsoft Excel 2007 (upgrade)
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/Chris
Cell A2 has Test/Charles
Cell A3 has Procedure/Tom
Cell A4 has Procedure/Steve

Cell B1 has Microsoft
Cell B2 has Microsoft
Cell B3 has Excel
Cell B4 has Excel

Output:
Cell C1: Chris Microsoft
Cell C2: Charles Microsoft
Cell C3: Tom Excel
Cell C4: Steve Excel

So 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

Report •

✔ Best Answer
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 Validation
to make sure everything is entered in a consistent manner.

MIKE

http://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))&" "&B1

Drag down as many rows as needed

MIKE

http://www.skeptic.com/


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))&" "&B1

Click 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/Chris
Cell A2 has Test//Tomorrow/Charles
Cell A3 has Procedure/Tom
Cell A4 has Procedure/Steve

Cell B1 has Microsoft
Cell B2 has Microsoft
Cell B3 has Excel
Cell B4 has Excel

Output:
Cell C1: Chris Microsoft
Cell C2: Charles Microsoft
Cell C3: Tom Excel
Cell C4: Steve Excel

I 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/Chris
Test//Tomorrow/Charles
Procedure/Tom
Procedure/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 indicator
2 - 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 from
Test/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
✔ Best Answer
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 Validation
to make sure everything is entered in a consistent manner.

MIKE

http://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,"/","")))))&" "&B1

MIKE

http://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.

MIKE

http://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 •

Ask Question