Share via

Cleaning data in excel

Anonymous
2023-11-20T21:21:23+00:00

Hello,

I have this list of data in excel (example below) which I would like to clean up can you help me? I have a list of 630 lines with different formats in the "old data" column which I would like to translate to the "cleaned up data" format. I don't know if I should send you the entire list.

Hope to hear from you.

Thank you

Dawn

Old data Cleaned up data
Attawee_E-Performance%202020-2021_Attawee%20Tharitchonla.pdf <br>Attawee_E-Performance%202022-2023_Attawee%20Tharitchonla.pdf <br>Attawee_E-Performance%202021-2022_Attawee%20Tharitchonla.pdf e-Performance 2020-21_Attawee Tharitchonla <br>e-Performance 2022-23_Attawee Tharitchonla <br>e-Performance 2021-22_Attawee Tharitchonla
Attawee_E-Performance%202020-2021_Rujirekh%20Jirarungruj.pdf <br>Attawee_E-Performance%202021-2022_Rujirekh%20Jirarungruj.pdf <br>Attawee_E-Performance%202022-2023_Rujirekh%20Jirarungruj.pdf e-Performance 2020-21_Rujirekh Jirarungruj <br>e-Performance 2021-22_Rujirekh Jirarungruj <br>e-Performance 2022-23_Rujirekh Jirarungruj
Pongsuwan10069338_2021_Supawat%20Pongsuwan.pdf Pongsuwan10069338_2022_Supawat%20Pongsuwan.pdf Pongsuwan10069338_2023_Supawat%20Pongsuwan.pdf
EP_EVAL%20PrachaJ%202020-2021_Pracha%20Jaichum.pdf <br>UN_EPERF_DOC%20PrachaJ%202021-2022_Pracha%20Jaichum.pdf <br>UN_EPERF_DOC%202022-2023%20PrachaJ_Pracha%20Jaichum.pdf
adiiko_Adam%20Shafi%20Ibrahim.pdf <br>adiiko%202_Adam%20Shafi%20Ibrahim.pdf
EPass%2019-20%20pdf_Yohannes%20Mitslal.pdf <br>Epass%2020-21_Yohannes%20Mitslal.pdf <br>epas%202022-2023_Yohannes%20Mitslal.pdf e-Performance 2019-20_Yohannes Mitslal <br>e-Performance 2022-23_Yohannes Mitslal
e-performance%20KOUEFODELS00304_2022_Trinite%20Heros%20Koue-F.pdf <br>e-performance%20KOUEFODELS00304_2019_Trinite%20Heros%20Koue-F.pdf <br>e-performance%20KOUEFODELS00304_2021_Trinite%20Heros%20Koue-F.pdf
Papa%20Plange_%202020-2021_EVAL_Papa%20Plange.pdf <br>Papa%20Plange_%202019-2020_EVAL_Papa%20Plange.pdf <br>Papa%20Plange_2018-2019_EVAL_Papa%20Plange.pdf
Evaluation%20PACE_Zakaria%20Hassan%20Ibrah.pdf <br>Evaluation%20Security%20Farchana_Zakaria%20Hassan%20Ibrah.pdf <br>Pace%20wfp%20Security%20Associate_Zakaria%20Hassan%20Ibrah.pdf
EP_EVAL%20BRANDFORD%202020-2021_Monique%20Racquel%20Bran.pdf <br>UN_EPERFORMANCE%20BRANDFORD%202021-%202022_Monique%20Racquel%20Bran.pdf <br>UN_EPERFORMANCE%20BRANDFORD%202022-%202023_Monique%20Racquel%20Bran.pdf
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

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-11-21T03:51:12+00:00

    sorry.

    =SUBSTITUTE(A2,"%20"," ")

    0 comments
  2. Anonymous
    2023-11-21T01:44:08+00:00

    Hi Dawn,

    Thanks for contacting us,

    Certainly, I can help you with cleaning up the data in Excel. Based on the examples you provided, it seems like you want to extract specific information from the "Old data" column and format it into a consistent structure in the "Cleaned up data" column.

    Here's a step-by-step guide on how you can achieve this using Excel formulas:

    1. **Extracting Text between underscores:**
      • Use the following formula to extract text between underscores for the names: 
             =MID(A2, FIND("\_", A2) + 1, FIND("\_", A2, FIND("\_", A2) + 1) - FIND("\_", A2) - 1)
        Drag this formula down for all rows in the "Cleaned up data" column, adjusting the cell references accordingly.
    2. **Extracting Text after the last underscore:**
      • Use the following formula to extract text after the last underscore for the document type: 
             =MID(A2, MAX(FIND("\_", A2, FIND("\_", A2) + 1), FIND("\_", A2, FIND("\_", A2, FIND("\_", A2) + 1) + 1)) + 1, LEN(A2))
        Drag this formula down for all rows in the "Cleaned up data" column, adjusting the cell references accordingly.
    3. **Concatenating Text:**
      • Finally, use a formula to concatenate the extracted information in the desired format: 
             ="e-Performance " & MID(A2, FIND("%", A2) + 1, 4) & "-" & MID(A2, FIND("%", A2) + 5, 2) & "\_" & [Name] & [Document Type]
        Replace [Name] and [Document Type] with the cell references of the formulas used in steps 1 and 2.

    After applying these formulas, you should have a cleaned-up format in the "Cleaned up data" column. Make sure to adjust the cell references in the formulas based on the actual cell locations in your Excel sheet. If you encounter any specific issues or if you have additional variations in the data, feel free to provide more details, and I can refine the guidance accordingly.

    I hope this helps!

    Regards, Sola

    0 comments
  3. Anonymous
    2023-11-21T01:09:31+00:00

    Thank you but it did not work. I am getting "value" error.

    Dawn.

    0 comments
  4. Anonymous
    2023-11-21T00:57:54+00:00

    =replace(A2,"%20"," ")

    looks like decode url

    0 comments