0

I have a very awkward problem in excel. It's hard to explain it in text exactly so I will use a simple example which is the same problem:

I have the words written in cell as follows:

A1   A2    A3  A4

Cat  HAZ  ARD  ?

Now in cell A4 i want to use the concatenate formula on A2 and A3 to get HAZARD; but i want to use the "cat" in A1 to construct the formula e.g. ="con"&A1&"enate"(A2,A3).

This ideally should translate to =concatenate(A2,A3) but it is giving me error.

Any workarounds?

0m3r
  • 12,286
  • 15
  • 35
  • 71
Hasan Zaheer
  • 1
  • What's your actual use here - why are you trying to build function names instead of just typing them out as a normal function? Maybe there's a better approach to your end goal. – DACrosby Jan 17 '16 at 07:37
  • Native excel functions can't do this. But you could create a UDF that Evalutes the string (using Evaluate) – chris neilsen Jan 17 '16 at 08:10
  • This old post might help you and here is the [link.](https://stackoverflow.com/questions/4471884/how-to-turn-a-string-formula-into-a-real-formula) – ian0411 Aug 17 '17 at 20:36

1 Answers1

0

Perhaps a macro could handle this task.

Private Sub Worksheet_Change(ByVal Target As Range)
    If UCase(target.Value) = "CAT" Then
        target.Offset(0, 3) = target.Offset(0, 1).Value & target.Offset(0, 2).Value
    End If
End Sub

What this macro does is: whenever a cell shows the value of "cat" or "Cat" or "CAT" anywhere on the sheet, the program will recognize this and in the 3rd cell over to the right, merge the first and second cell to the right.

PM me if you've never used macros before and I'll walk you through the process; however I'm sure once your good with macros you'll come up with far more superior laziness shortcuts than this.

Cheers

ben mazor
  • 41
  • 2
  • 9