0

I have a list of positions + company names. Example: "senior manager at xxx company".

I need to separate the information that comes after the "at", so I can get only the company name. Which formula can I use to do that?

Many thanks in advance, Ana

1
  • You nay use =RIGHT(A1,LEN(A1)-FIND("at ",A1)-1) & fill down. May 29, 2020 at 5:53

3 Answers 3

1
B1 = left(A1,find(" at ",A1)-1)
C1 = mid(A1,find(" at ",A1)+3,len(A1))
1
  • This is mostly correct but should say +4 in the second formula, not +3 May 27, 2020 at 21:44
0

Assuming the data is in cell A1, you can enter the following formula in cell B1:

=MID(A1,FIND("at",A1)+2,99)

enter image description here

0

if you are not a big fan of formulae enter the first company name manually in the adjacent column here cell B2 and drag as you would while filling a series but this time just click flash fill instead of the default fill series

Screenprint is available for your reference. This option works with Excel 2013 onwards.

Flash Fill

Final Output after Flash Fill

Final Output

5
  • 1
    You mean to say Drag Separated Text,,, what if each cell has different company name after at ? May 29, 2020 at 5:35
  • you need to type the first company name and then drag it then select Flashfill. It does not matter if you have a different company name as this works like a charm(please check the final output image). It also works if you dont have 'at' also.
    – meetrk85
    May 29, 2020 at 5:43
  • ,, remember the Flash fill works in adjacent column only and the formula can be used any where, even on different sheet/workbook !! May 29, 2020 at 5:50
  • Very true sir but still Flashfill is a good option to use and be familiar with
    – meetrk85
    May 29, 2020 at 6:00
  • Yes it nice one but has limited scope,,, and a good answer has to consider every aspect ☺ May 29, 2020 at 6:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .