Concatenating different formats

[High77]

Hi,

I have created the following table:

C2 looks for the last entry of that row.
D2 looks up the Header for the last entry on the row.
E2 is a concatentate of both, as a straight formula and is the column I'll be keeping. (I'll remove columns C & D later, as they were my test cells for the formula).

This works great for whenever the last entry on the row is dropdown text. However when it is a date is throws a wobbly and spits out a number see C2 and the 2nd part of E2.

I've found how to concatenate to "fix" it as a date, however in this particular instance, some times the information will be normal text and other times it will be a date.

Any ideas how to concatenate so that when it's text it shows correctly eg WWN, then when its a date show 14/3/20 format rather than 44592 format?

It it's not possible, I'll have to keep columns C & D and forget Concatenating.

Regards,

Dave

 

[etaf]

how does
=F2&" "&IF(ISNUMBER(G2),TEXT(G2,"DD/MM/YY"),G2)
work for you ?
may need to change the columns - as i did not use yours
B is the concatenation of F & G

Various.xlsx
	A	B	C	D	E	F	G
1
2		Fred 01/01/21				Fred	1/1/21
3		fred john				fred	john
4
Sheet12

Cell Formulas
Range	Formula
B2:B3	B2	=F2&" "&IF(ISNUMBER(G2),TEXT(G2,"DD/MM/YY"),G2)

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

XL2BB is an add-in used on this forum , see my signature or the menu in post to link to the full details
OR put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

Here is the link to XL2BB instructions

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Peter_SSs]

I don't think that you need to test for a number?

22 02 21.xlsm
	C	D	E
1
2	Interview	44592	Interview 31/1/22
3	Element	WWN	Element WWN
Concat formats

Cell Formulas
Range	Formula
E2:E3	E2	=C2&" "&TEXT(D2,"D/M/YY")

 

[Rick Rothstein]

I am not sure this is any "better", but here is another way to write the formula Peter posted (same length but it eliminates one concatenation)...

=C2&TEXT(D2," D/M/YY;; @")

 

[Peter_SSs]

same length

Not by my count. ?

 

[Rick Rothstein]

Not by my count. ?

Hmm, I miscounted by one. Okay, now they are the same length...

=C2&TEXT(D2," D/M/YY; @")

but now I am confused. In my original formula, I meant to use 3 semi-colons, not 2, in order to put the space/at characters in the text portion of the format pattern, but it worked with only two semi-colons which meant it worked with the space/at characters in the "zero" position of the format pattern. Given that, I tried using only one semi-colon (the above formula)... and it worked!!! Why??? I know you can do something similar if you provide a conditional test in square brackets on the first term, but that is not being done here. Can you or do you know of any documentation that explains why the pattern I used above should work?

 

[High77]

how does
=F2&" "&IF(ISNUMBER(G2),TEXT(G2,"DD/MM/YY"),G2)
work for you ?
may need to change the columns - as i did not use yours
B is the concatenation of F & G

Various.xlsx
	A	B	C	D	E	F	G
1
2		Fred 01/01/21				Fred	1/1/21
3		fred john				fred	john
4
Sheet12

Cell Formulas
Range	Formula
B2:B3	B2	=F2&" "&IF(ISNUMBER(G2),TEXT(G2,"DD/MM/YY"),G2)

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

XL2BB is an add-in used on this forum , see my signature or the menu in post to link to the full details
OR put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

Here is the link to XL2BB instructions

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

That worked perfectly thank you very much.

 

[etaf]

you are welcome, but i think you have better answers from other members

 

[High77]

I don't think that you need to test for a number?

22 02 21.xlsm
	C	D	E
1
2	Interview	44592	Interview 31/1/22
3	Element	WWN	Element WWN
Concat formats

Cell Formulas
Range	Formula
E2:E3	E2	=C2&" "&TEXT(D2,"D/M/YY")

etaf is indeed correct. Your solution is shorter and works really well. Thank you for your help. I really appreciate it.

 

[Peter_SSs]

You're welcome.