04-06-2011, 11:46 AM

Hi newbie here,

running excel:mac 2011 on MBP

I understand this is a bit of a long shot, but for a university project I have a massive list of data which has been compiled by two different researchers who have, somewhat annoyingly, coded their results in two different ways.

Is there a formula/tool in excel that allows you to do a mass edit of what is written in a cell?

I would settle for the actual words to search for; I think it's a string? or substring? and may have something to do with concatenation.

As an example, say half of the data is written in the format x-y-z, and the other half is x-z-y, is there a way to change one format to another without going through 16,000 cells retyping what's in them?!

Any help whatsoever would be massively appreciated

Examples of the actual two codes are shown below. Each different code format contains the same information just in a different order. I have colour coordinated the corresponding bits of data from each code which I would like to put into the same order. I hope that's not too cryptic and may make it easier to understand what the h*ll i'm talking about! I Would like the second format to become like the first.


7911_M_1-1-3_014_L

6083F101L1001

many thanks

04-07-2011, 12:46 AM

Howdy. It has been 3+ years since I worked extensively in Excel. But there are functions that allow you to determine order by counting either from the left or the right. While it could be done in one move, it would result in a difficult formula to set up. I can give you how to get rid of the unwanted characters.

ALWAYS work on a backup copy in case things turn bad. Assume your data is in Column C, beginning in row 2:

Select the column in which the data appears, then do a find and replace of the underscore (i.e. find it, then replace it with nothing).

Or you can use a formula in D2

=SUBSTITUTE(C2,"_","")

Copy this formula down the entire list by moving cursor to lower right corner of cell, and when it turns into a cross hairs, then double click and it will fill the entire length of the column.

Copy column D and paste back onto itself using "paste special values."

Then use the formula again on column D, and put this in E2

=SUBSTITUTE(D2,"-","")

As for the rest of how to do it, it would be best to visit the best Excel help site to for answers is Mr Excel. I used to participate regularly when using Excel everyday. But I don't even have Excel anymore.