Click here for important information about Computing.net.

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/SteveCell B1 has Microsoft

Cell B2 has Microsoft

Cell B3 has Excel

Cell B4 has ExcelOutput:

Cell C1: Chris Microsoft

Cell C2: Charles Microsoft

Cell C3: Tom Excel

Cell 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.

✔ 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 ExcelYou 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 ExcelThen you can use some type of Data Validation

to make sure everything is entered in a consistent manner.MIKE

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

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.

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.

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/SteveCell B1 has Microsoft

Cell B2 has Microsoft

Cell B3 has Excel

Cell B4 has ExcelOutput:

Cell C1: Chris Microsoft

Cell C2: Charles Microsoft

Cell C3: Tom Excel

Cell 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.

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.

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!

You've lost me here. If you start with

Test/Today/Chrisand 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

familyChrisunless 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.

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 ExcelYou 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 ExcelThen you can use some type of Data Validation

to make sure everything is entered in a consistent manner.MIKE

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

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.

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!

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Discuss in The Lounge

Poll History