Share via

Excel: How to remove 9 characters from left & 2 characters from right. Text string remaining varies in length. (LEFT & RIGHT in single formula?)

Anonymous
2014-01-21T10:16:32+00:00

I have a column of cells containing text in which each cell starts with the 9 characters "Company: " and ends with the 2 characters " ." (Ignore the quotation marks). I want to return - in a new column - just the text that's sandwiched between those initial 9 and final 2 characters.

I can achieve what I need using two functions in separate columns ... the LEFT function in one column gets rid of the unwanted last two characters, and then a RIGHT function working on the result that the LEFT function has returned, gets rid of the unwanted first 9 characters.

What is the best way to achieve the result needed using only one formula (in a single column)?

Hopefully my screen capture illustrates what I mean. I welcome any answers and suggestions.

Microsoft 365 and Office | Excel | For home | 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
    2014-01-21T10:43:45+00:00

    Hello,

    If ALL your original data values end with a space and a dot and start with "Company: " as in

    Company: some data .

    Then you can use

    =MID(A1,10,LEN(A1)-11)

    40+ people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-01-21T11:42:37+00:00

    You missed the MID function :)

    =MID(A1,9,LEN(A1) - 11)

    Thanks Graham: Looks like the precise answer I need in my particular example is

    =MID(A1,10,LEN(A1) - 11)

    4 people found this answer helpful.
    0 comments
  2. Anonymous
    2014-01-21T10:40:32+00:00

    You missed the MID function :)

    =MID(A1,9,LEN(A1) - 11)

    1 person found this answer helpful.
    0 comments