Search code examples
excelexcel-formuladata-cleaning

Cleaning Data in excel

I am trying to clean the data I have this first column that I extracted the city code but there is a problem the state code I could not extracted because there is no way to extract it other than manually as far as I know. I tried using chatgpt but I couldn't reach to a solution. I used the split function to split using spaces but there are some cities that doesn't have two spaces so the state code goes to the city2 column

what could be best technical solution for this

enter image description here

I did try the splitting the function

Solution

  • Split Delimited Data

    enter image description here

    =LET(data,A2:A21,lmDelimiter,"-",StateChars,2,Comma,",",
    d,SUBSTITUTE(data,Comma,""),
    l,IFERROR(TRIM(TEXTBEFORE(d,lmDelimiter,,1,,"")),""),
    rt,TRIM(TEXTAFTER(d,lmDelimiter,,1,,TRIM(d))),
    rc,RIGHT(rt,StateChars),e,EXACT(rc,UPPER(rc)),
    m,IFERROR(IF(e,TRIM(LEFT(rt,LEN(rt)-2)),rt),""),
    r,IFERROR(IF(e,rc,""),""),
    dr,HSTACK(l,m,r),
dr)
    • It is based on user11222393's idea "all state codes are 2 upper letters".
    • Replace the last dr with any of the other variables to see what they return.