Share via

Extracting only the ID from the rest

Anonymous
2024-03-26T18:27:07+00:00

Hi there,

I must only extract the ID from values such as Lisa Francisco (W2349864). There are 1200 names in the ID embedded in parenthesis that I need to extract only the W2349864 # and put it in a separate column.

I greatly appreciate your time.

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. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-03-26T22:51:50+00:00

    Hi,

    In cell B2, enter this formula and drag down

    =TEXTBEFORE(TEXTAFTER(A2,"("),")")

    Hope this helps.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2024-03-26T18:56:17+00:00

    There are many ways to do this. An easy way although it has many steps is to use your Text to Columns option.

    Highlight the column with your data, in this case I'm only using what you've provided.

    Go to your data tab and find "Text to Columns".

    Once you click that option a window for a wizard should pop up. Select the delimited option and click next.

    On this second page remove the other delimiters and only select other, put your open parenthesis, you'll see the data preview split the data into 2 columns at the delimiter you use, and click next.

    In this third page you can choose the format for the new columns and/or choose to remove columns you won't be needing.

    For example, here I'm removing the column that will be left with the name. You can tell that because it says Skip Column in the Data Preview window on the column with the name info, if you choose to leave the name use General or Text in your Column data format. You can select the column with your id and format it if you want but for now, I will leave it this way since I need to remove the close parenthesis. Click Finish.

    If you followed these steps this is what your data should look like now.

    In order to remove the closed parenthesis, repeat the process with the closed parenthesis. Scroll up for all the steps this screenshot is just a brief recap.

    Once done you should have just id's.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-03-26T19:47:35+00:00

    I know I gave one answer, but this is my preferred way especially if this is a task that is constantly repeated, since this will allow you to just replace the data and press refresh to get new id's extracted. I'm assuming you have a newer version of excel. I made some data to show how this would work with your 1200 rows.

    1. Click anywhere in the column which has your data.
    2. From your ribbon click on the Data tab
    3. In the Data tab click "From Table/Range"
      1. This should highlight your 1200 rows of data, assuming you have no empty rows.
    4. In the pop up you should also see the range for all your rows.
    5. Select the checkbox "My table has headers" if you have header row.
    6. Click Ok

    This should open power query.

    1. Ensure the column with your id's is selected.
    2. If you just want the id's, click the transform tab if you want to keep the original data while getting the id's in a separate column, click on Add Column
    3. Find and click on the Extract option.
    4. From the drop down select Text Between Delimiters
    5. Put your open parenthesis in the start delimiter.
    6. Put your close parenthesis in the end delimiter.
    7. Click ok.

    At this point your data should be extracted. Go to Close & Load to... to export it to your spreadsheet.

    At this point I would just put my data to the right of the original data:

    1. In the Import Data window select Table
    2. Then select Existing worksheet
    3. I then select cell B1
    4. Finally click on Ok

    This is what it should look like:

    Now anytime I need to get new id's I can just remove the old info and put in the new info and refresh.

    1. Changed the data in the left column and click refresh all
    2. New Id's that were extracted

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-03-26T19:29:45+00:00

    Thank you Larry. I'll try this method as well. I greatly appreciate your help!

    Was this answer helpful?

    0 comments
  3. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2024-03-26T18:49:01+00:00

    Use TextToColumns with ( as separator https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

    After that search for ) and replace with nothing, done.

    Andreas.

    Was this answer helpful?

    0 comments