Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a string in a cell:

ABCDxxxxxEFGH

ABCD and EFGH are constants, and xxxxx is of variable length.

How do I write an Excel formula to extract xxxxx?

share|improve this question
add comment

migrated from stackoverflow.com Sep 22 '09 at 2:44

This question came from our site for professional and enthusiast programmers.

2 Answers

Assuming it is in Cell A1

=MID(A1,5,LEN(A1)-8)

If you wanted to check that the cell is correctly formed you could do:

=IF(LEN(A1)>8,IF(AND(LEFT(A1,4)="ABCD",RIGHT(A1,4)="EFGH"),MID(A1,5,LEN(A1)-8),NA()),NA())
share|improve this answer
    
+1 for the format check –  Tracy Probst Oct 7 '09 at 16:09
add comment

If you just want to brute force it (assuming ABCD and EFGH aren't within xxxxx then the following approach should work, although I would recommend you come up with a better method.

myvariable = split(split(cell, "ABCD")(1), "EFGH")(0)

(Please excuse if my syntax is slightly off. It's been a while since I've been in VBScript.)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.