formula help

[CRAIG20]

Hi. I have a formula for cell F7 The formula is =E7+$L$2. E7 contains a number (18). L2 also contains a number (0.5) so it adds 18+0.5 However if E7 contains a number with NIE preceding it eg. NIE15 I would like it to ignore the'NIE' and just add 0.5 to 15

Thanks in advance

 

[Gerald Higgins]

Hi.

Perhaps like this . . .

=IF(ISNUMBER(E7),E7,MID(E7,4,100))+$L$2

This will ignore any initial text string of exactly 3 characters at the beginning of the entry in E7, for example "NIE", "OJF", etc.

Will NOT work correctly for text strings such as "NI", or "NIEZ", where the number of alpha characters is not exactly 3.

 

[CRAIG20]

Thats perfect. One thing I forgot to mention is it possible to add the 'NIE' to the start of the result. eg. with your formula NIE18 changed to 18.5 But is it possible to make it NIE18.5?

 

[Gerald Higgins]

Try this . . .

=IF(ISNUMBER(E7),,LEFT(E7,3))&IF(ISNUMBER(E7),E7,MID(E7,4,100))+$L$2

This adds the first three characters from E7 to the start of the result, IF (and ONLY IF) the value in E7 is a text string, not a number.
Try it with different values.

NOTE - if E7 DOES start with "NIE", the result of this formula will be a text string, which will be more difficult to use in arithmetical formulas.

 

[CRAIG20]

Thats perfect. Many thanks for your help!