unable to record a formula as its too long

[aravindhan_31]

Hi,

I tried recording this formula I am not able to record as system prompts me "Unable to Record"
How Do i write this formula in a code?

Please Login or Register  to view this content.

Arvind

[DonkeyOte]

Arvind, I could sit here for a while working this through but in essence you seem to be trying to pick up a given word from within a string, care to post an example of what you're doing ?

As a general pointer - when you have a lengthy formulae and want to error trap you can avoid the double evaluation by using LOOKUP in conjunction with CHOOSE (when you're returning a consistent data type as is the case here (string))

LOOKUP(REPT("Z",255),CHOOSE({1,2},"",your formula))

BUT that said I suspect we can dramatically shorten your formula ... need some samples first though to be sure.

[aravindhan_31]

Hi,

Thanks for your reply
I have the value in sheet "Import" and in cell Ak2 as "Leonard Berly"

my result in AL2 should be L Berly INT
that is First letter of the Firstname & full lastname and a word INT
So If i input the below formula I get the result, if error it result blank.

=IF(ISERROR(LEFT(Import!AK2,1)&" "&LEFT(RIGHT(Import!AK2,LEN(Import!AK2)-FIND(" ",Import!AK2,1)),FIND(" ",RIGHT(Import!AK2,LEN(Import!AK2)-FIND(" ",Import!AK2,1)),1))&"INT"),"",LEFT(Import!AK2,1)&" "&LEFT(RIGHT(Import!AK2,LEN(Import!AK2)-FIND(" ",Import!AK2,1)),FIND(" ",RIGHT(Import!AK2,LEN(Import!AK2)-FIND(" ",Import!AK2,1)),1))&" INT")

I was wondering y do I get " unable to record" option

Thanks again..

Arvind

[squiggler47]

Does this work for you?

=IF(B4="","",SUBSTITUTE(B4,MID(B4,2,FIND(" ",B4&" ")-2),"")&" INT")

[aravindhan_31]

Hi,

Thanks for you help.
The formula works fine when there are only 2 words in Cell B4,
like Alan David But if the name is Alan David Jone then the formula results A David Jones Int
The result required is A David Int

Thanks
Arvind

[squiggler47]

Ok then :-

Please Login or Register  to view this content.

[DonkeyOte]

Arvind, perhaps you could use:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",TRIM(LEFT(AK2,1)&" "&REPLACE(AK2,1,FIND("#",SUBSTITUTE(AK2," ","#",LEN(AK2)-LEN(SUBSTITUTE(AK2," ","")))),"")&" INT")))

The above will return a Null if the base formula returned an Error.

[aravindhan_31]

Hi,

Perfect, all the formulas are working

Thank you all

Arvind

[protonLeah]

Maybe:

Please Login or Register  to view this content.