CaptainGravyBum
Board Regular
- Joined
- Dec 1, 2023
- Messages
- 70
- Office Version
- 365
- Platform
- Windows
Hello,
I have a formula in column A which is building a specific number format based on the characters provided from column B. This has been working fine when it was a fixed amount of numbers, but the spanner in the works is we now have text in addition to a the number. I've tried to adjust my formula to accommodate, but I can't get it right. Can anyone help?
I have a formula in column A which is building a specific number format based on the characters provided from column B. This has been working fine when it was a fixed amount of numbers, but the spanner in the works is we now have text in addition to a the number. I've tried to adjust my formula to accommodate, but I can't get it right. Can anyone help?
Book1.xlsx | ||||
---|---|---|---|---|
A | B | |||
1 | +++85931489-02 | 85931489-2 | ||
2 | +++85947129-01 | 85947129-1 | ||
3 | GECV8595251101 | GECV85952511-1 | ||
4 | GECV8595251101 | GECV85952511-1 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1:A4 | A1 | =IF(ISNUMBER(SEARCH("GEC",B1)),TEXT(LEFT(B1,12),"+++####00000000-")&TEXT(RIGHT(B1,LEN(B1)-FIND("-",B1)),"00"),TEXT(LEFT(B1,8),"+++00000000-")&TEXT(RIGHT(B1,LEN(B1)-FIND("-",B1)),"00")) |