CODEWITHSUNDEEP
excelexcel-formula
David Leal
David Leal

Reputation: 6759

Return the result of an excel function on the same column of the input argument

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.

Upvotes: 1

Views: 116

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

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.

Upvotes: 1

Related Questions