Looking for a code or formula what can identify any text and change this into a specific word

[sjoerdbosch]

I am looking for a macro or formula what can identify a cell what contains combination of numbers and letters into a word
The combination of letters and numbers is on another sheet than the destination word.
The sheet with the combination is in sheet what is called "Ullage Report" and this is sheet 16 in VBA and the target sheet is "STOWAGE" which is sheet 20 in VBA
The source cells in Ullage - [AF34] - can contain a number - or a number + Letter
If only a number - I want to display in the target cell [STOWAGE N2] the word Annex II and if number(s) + letter it must be Annex I

Had the formula below in the cell in the stowage sheet, and which only works if AF34 is more than 0 but it does not work if the cell contains e.g. 54B
Should that be the case it should display Annex I
=IF('Ullage Report'!P35="","",IF('Ullage Report'!AF34>0,"Annex II","Annex I"))

Also if the cell Ullage Report'!P35="","", [should display nothing]

 

[Skybluekid]

Have you any data we can work with?

 

[sjoerdbosch]

Have you any data we can work with

hi. not sure what kind of data i can give you. thought all information needed is in the question
the formula i am looking for should work for any cell which i assign in the formula or code

 

[Skybluekid]

Try this

=IFS('Ullage Report'!P35="","",ISNUMBER('Ullage Report'!AF34),"Annex II",TRUE,"Annex I")

This is my mock up

Book1.xlsx
	B	C
2	54	Annex II
3	54B	Annex I
4	412	Annex II
5	412C	Annex I
Sheet5

Cell Formulas
Range	Formula
C2:C5	C2	=IF(ISNUMBER(B2),"Annex II","Annex I")

 

[sjoerdbosch]

hmm

Try this

=IFS('Ullage Report'!P35="","",ISNUMBER('Ullage Report'!AF34),"Annex II",TRUE,"Annex I")

This is my mock up

Book1.xlsx
	B	C
2	54	Annex II
3	54B	Annex I
4	412	Annex II
5	412C	Annex I
Sheet5

Cell Formulas
Range	Formula
C2:C5	C2	=IF(ISNUMBER(B2),"Annex II","Annex I")

. i get name errors on the formula - most likely because there is a formula in the reference cell - so not empty and also on Annex II statement I get a name error if i remove the first part of the formula.
the mock up - i did not try - can't make sense out of that

 

[Skybluekid]

I used the IFS function as you have indicated that you are using 365.

Alternative is,
=IF('Ullage Report'!P35="","",IF(ISNUMBER('Ullage Report'!AF34),"Annex II","Annex I"))

 

[sjoerdbosch]

I used the IFS function as you have indicated that you are using 365.

Alternative is,
=IF('Ullage Report'!P35="","",IF(ISNUMBER('Ullage Report'!AF34),"Annex II","Annex I"))

hi
i also use 2016 on other PC's where the program also has to work and that does not have that function

 

[Skybluekid]

@sjoerdbosch Is it the ISNUMBER Function? This was introduced into excel in 2000, so it should be available.

 

[sjoerdbosch]

@sjoerdbosch Is it the ISNUMBER Function? This was introduced into excel in 2000, so it should be available.

Found a function what does the job. Thanks!

=IF(J13="","",IF(ISNUMBER('Ullage Report'!Q50),"Annex II","Annex I"))

 

[smozgur]

Found a function what does the job. Thanks!

=IF(J13="","",IF(ISNUMBER('Ullage Report'!Q50),"Annex II","Annex I"))

The function and implementation have been provided in post #6, references might change for your needs. Therefore, the marked solution has been switched accordingly.