Share via

IF LEN Function help

Anonymous
2023-07-13T16:02:45+00:00

=IF(LEN(D7)>9),REPLACE(D7,9,10,""),REPLACE(D7,8,9, "")

Hello, this is the logic:

If the length of the D7 is longer than 9 characters, replace the 9th and 10th character with "", if not, replace the 8th and 8th position with "".

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2023-07-13T16:13:55+00:00

    =IF(LEN(D7)>9,MID(D7,1,8)&MID(D7,11,100),MID(D7,1,7)&MID(D7,10,100))

    Image

    If your max characters is 10, you may use this one.

    =IF(LEN(D7)>9,MID(D7,1,8),MID(D7,1,7))

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rory Archibald 18,875 Reputation points Volunteer Moderator
    2023-07-13T16:16:43+00:00

    The second number is the number of characters to replace, not the ending position, so it would be:

    =IF(LEN(D7)>9,REPLACE(D7,9,2,""),REPLACE(D7,8,2, ""))

    or:

    =,REPLACE(D7,IF(LEN(D7)>9,9,8),2,"")

    0 comments
  2. Anonymous
    2023-07-13T16:49:05+00:00

    This was so helpful thank you so much!

    0 comments