1

Is there a way to invoke an excel function on a given column and return the result on the same column?, so I don't need to create a new column for getting the transformed column. For example, let's say I would like to have a column A in upper case format, then I would like to apply the function UPPER(A:A), but replacing the value of the column A. Is there a way to do it?

All string manipulation function should be used in such a way. Usually, functions that have only one input argument.

The new excel functionality Power Query allows making simple transformations (but it does not have the power of excel functions) to the columns, but I am thinking of something with standard excel functionality.

David Leal
  • 6,373
  • 4
  • 29
  • 56

1 Answers1

1

While it's pretty limited, you can do this with some of the formats.

Highlight your column, and then go to Find -> Replace (or CTRL+H).

In the "Find What", type ? then leave "Replace With" empty.

Then, in the "Replace With" row, click "Format". Here you can adjust it to be whatever you can choose. Unfortunately, I don't see a way to apply all UPPER CASE, but you could Bold, Underline and a few other things.

Once that's set, just hit "Replace All" and it'll update your text with that chosen format.

enter image description here

FWIW - I even had a separate cell C2 with TEST, and D2 with =UPPER(C2) and chose in my find/replace format "Choose Format from Other Cell", hoping it'd realize that either one was all caps, but that didn't work. So I think the only formats you can do like this are what pops up in the "Replace Format" window.

Edit: For uppercase specifically, you'd have a much easier time doing this in VBA (it's even a one line command you could do in the Immediate Window - [A1:A200] = [INDEX(UPPER(A1:A200),)], if you really wanted this done without a helper column/VBA, I suppose you could install an all caps font, then choose that font as the replacement.

Community
  • 1
  • 1
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • @BrucyWayne, the upper function was just an example, I have a more complex situation on my real example. I have a semi-colon delimited string and I just need the last one element ([here](https://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba) is a solution, see the answer from @JoeFinkle), but I don't really need the entire string, so adding just a column for that is ugly, but it seems to be the only way. Trying to solve this, the question of my post arises. – David Leal Jul 01 '19 at 19:55