SaraWitch
Active Member
- Joined
- Sep 29, 2015
- Messages
- 378
- Office Version
- 365
- Platform
- Windows
Hello peeps,
I have a formula to combine text and return dates where there is one, which works just as I want it to, except I don't want anything to return for blank cells. I know I could probably use CONCAT or JOINTEXT (but I couldn't find a way of returning dates in my range and I want different separators). I would also like to start each person on a new line (I tried &CHAR(10) but that didn't work). I also know that I can adjust the column width, but I have a large dataset that won't fit nicely into a determined width.
Any help would be greatly appreciated
I have a formula to combine text and return dates where there is one, which works just as I want it to, except I don't want anything to return for blank cells. I know I could probably use CONCAT or JOINTEXT (but I couldn't find a way of returning dates in my range and I want different separators). I would also like to start each person on a new line (I tried &CHAR(10) but that didn't work). I also know that I can adjust the column width, but I have a large dataset that won't fit nicely into a determined width.
¦ MrExcel Queries.xlsm | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
1 | Name | Gender | DoB | Age | Name | Gender | DoB | Age | Name | Gender | DoB | Age | Name | Gender | DoB | Age | Household members | What I would like! | ||||||
2 | Aerwyna | Witch | (F) | 31/10/1978 | 45 | Lub | Lubber | (M) | 31/10/1965 | 58 | Prince | Goblin | (M) | 31/08/2020 | 3 | Princess | Faerie | (F) | 31/10/2023 | 0 | Aerwyna Witch (F) - 31/10/1978, 45 Lub Lubber (M) - 31/10/1965, 58 Prince Goblin (M) - 31/08/2020, 3 Princess Faerie (F) - 31/10/2023, 0 | Aerwyna Witch (F) - 31/10/1978, 45 New line Lub Lubber (M) - 31/10/1965, 58 New line Prince Goblin (M) - 31/08/2020, 3 New line Princess Faerie (F) - 31/10/2023, 0 | ||
3 | Lub | Lubber | (M) | 31/10/1965 | 58 | Prince | Goblin | (M) | 31/08/2020 | 3 | Princess | Faerie | (F) | 31/10/2023 | 0 | Lub Lubber (M) - 31/10/1965, 58 Prince Goblin (M) - 31/08/2020, 3 Princess Faerie (F) - 31/10/2023, 0 - 00/01/1900, | Lub Lubber (M) - 31/10/1965, 58 New line Prince Goblin (M) - 31/08/2020, 3 New line Princess Faerie (F) - 31/10/2023, 0 If empty cells in range, don't return, i.e.: - 00/01/1900 | |||||||
4 | Prince | Goblin | (M) | 01/09/2020 | 3 | Princess | Faerie | (F) | 01/11/2023 | 0 | Prince Goblin (M) - 01/09/2020, 3 Princess Faerie (F) - 01/11/2023, 0 - 00/01/1900, - 00/01/1900, | Prince Goblin (M) - 31/08/2020, 3 New line Princess Faerie (F) - 31/10/2023, 0 If empty cells in range, don't return, i.e.: - 00/01/1900, If empty cells in range, don't return, i.e.: - 00/01/1900, | ||||||||||||
Join Text |
Cell Formulas | ||
---|---|---|
Range | Formula | |
T2:T4,J2:J4,E2:E4,O2:O3 | T2 | =IF(S2="","",DATEDIF(S2,TODAY(),"Y")) |
U2:U4 | U2 | =A2&" "&B2&" "&C2&" - "&TEXT(D2,"dd/mm/yyyy")&", "&E2&" "&F2&" "&G2&" "&H2&" - "&TEXT(I2,"dd/mm/yyyy")&", "&J2&" "&K2&" "&L2&" "&M2&" - "&TEXT(N2,"dd/mm/yyyy")&", "&O2&" "&P2&" "&Q2&" "&R2&" - "&TEXT(S2,"dd/mm/yyyy")&", "&T2 |
Any help would be greatly appreciated