0

I have a list of 1000s of different labels and need to remove the space between the number and mg. Sometimes the space is present and other times it's not. The position of the text "mg" is not the same in every cell (there might be four words before it or only one) so I assume vba code would be the easiest option.

An example of part of the list:

IBUPROFEN 5 MG TAB
IBUPROFEN 5MG TAB
IBUPROFEN 5 MG TAB
IBUPROFEN 5MG TAB
ESTRADIOL 0.5 MG TAB
ESTRADIOL 0.5 MG TAB
ESTRADIOL 0.5MG TAB
ESTRADIOL 0.5MG TAB
ESTRADIOL 0.5MG TAB
ATORVASTATIN TAB 20MG
ATORVASTATIN TAB 20 MG
ATORVASTATIN TAB 20 MG
ATORVASTATIN TAB 20 MG
3
  • What have you tried? Please edit your post to include scripts, formulas, code, or anything else that would help people to help you.
    – Cfinley
    Feb 26, 2015 at 18:16
  • @imtheman, are you sure Stacy's list is in multiple cells? What if the list is a chunk of text all in one cell? Stacy, please clarify the structure of your list within Excel.
    – hBy2Py
    Feb 26, 2015 at 18:22
  • 1
    Please don't remove the content of the question once it's been solved. Instead, click the green check-mark next to the answer that solved your problem, this will mark it as answered. Mar 2, 2015 at 17:24

2 Answers 2

1

I doubt that the text " MG" appears anywhere except in the cases you want to replace. Mganga (witch doctor) is a possibility but I really doubt it's in there. If that's the case, you can do a simple find + replace to replace " MG" with "MG".

If there are any cases where you have " MG" and don't want "MG", then Brian's answer is more complete.

0

Assuming it is guaranteed that the "MG" always has a following space:

If you can be sure that your test is always all-uppercase, the following should work:

=SUBSTITUTE(A1," MG ","MG ")

If you are fine with any lowercase text being converted to uppercase, the following will work:

=SUBSTITUTE(UPPER(A1)," MG ","MG ")

Otherwise, you will need to nest SUBSTITUTE function calls to handle all possible upper/lower case scenarios:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," MG ","MG ")," mg ","mg ")," Mg ","Mg ")," mG ","mG ")

If it is possible that one or more 'MG' occurrences may not be followed by a space, it will be challenging to do what you want. The use of VBA and regular expressions will almost certainly be necessary.

4
  • Based on the example text, it appears that "MG" can appear at the end such that it will not be followed by a space. If this is a one-time operation, OP could add another helper column to the right with a function like: =IF(RIGHT(UPPER(B1),3)<>" MG",B1,LEFT(B1,LEN(B1)-3)&"MG") Feb 26, 2015 at 18:39
  • It's also quite likely that there are no occurrences of " MG" except for the cases OP wants to replace. In that case, a simple FIND+REPLACE would work fine. Mganga (witch doctor) is a possibility but I would venture a guess that it's not in the list. Feb 26, 2015 at 18:44
  • @EngineerToast, the structure of the list was set up by an edit to the original post. I am awaiting Stacy's confirmation of the actual structure of her data. If it is as imtheman has structured it, then I agree, my answer does not solve the problem. You make a good point, the frequency of erroneous replacements on " MG" may well be functionally nil.
    – hBy2Py
    Feb 26, 2015 at 19:34
  • The list is in multiple cells with each title being a single cell. Ive attached an example
    – Stacy
    Mar 2, 2015 at 16:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .