ਐਕਸਲ ਵਿੱਚ ਆਮ ਡੇਟਾ ਸਫਾਈ ਫਾਰਮੂਲੇ

ਐਕਸਲ ਫਾਰਮੂਲੇ

ਸਾਲਾਂ ਤੋਂ, ਮੈਂ ਪ੍ਰਕਾਸ਼ਨ ਨੂੰ ਇੱਕ ਸਰੋਤ ਦੇ ਤੌਰ ਤੇ ਇਸਤੇਮਾਲ ਕੀਤਾ ਹੈ ਕਿ ਸਿਰਫ ਇਹ ਨਾ ਵਰਣਨ ਲਈ ਕਿ ਚੀਜ਼ਾਂ ਕਿਵੇਂ ਕੀਤੀਆਂ ਜਾਂਦੀਆਂ ਹਨ, ਬਲਕਿ ਆਪਣੇ ਆਪ ਨੂੰ ਬਾਅਦ ਵਿੱਚ ਵੇਖਣ ਲਈ ਇੱਕ ਰਿਕਾਰਡ ਵੀ ਰੱਖਦਾ ਹਾਂ! ਅੱਜ, ਸਾਡੇ ਕੋਲ ਇੱਕ ਗਾਹਕ ਸੀ ਜਿਸ ਨੇ ਸਾਨੂੰ ਇੱਕ ਗ੍ਰਾਹਕ ਡਾਟਾ ਫਾਈਲ ਸੌਂਪ ਦਿੱਤੀ ਜੋ ਇੱਕ ਤਬਾਹੀ ਸੀ. ਅਸਲ ਵਿੱਚ ਹਰ ਖੇਤਰ ਦਾ ਗ਼ਲਤ ;ੰਗ ਸੀ ਅਤੇ; ਨਤੀਜੇ ਵਜੋਂ, ਅਸੀਂ ਡੇਟਾ ਨੂੰ ਆਯਾਤ ਕਰਨ ਵਿੱਚ ਅਸਮਰੱਥ ਹਾਂ. ਜਦੋਂ ਕਿ ਵਿਜ਼ੂਅਲ ਬੇਸਿਕ ਦੀ ਵਰਤੋਂ ਕਰਦਿਆਂ ਕਲੀਨ ਅਪ ਕਰਨ ਲਈ ਐਕਸਲ ਲਈ ਕੁਝ ਵਧੀਆ ਐਡ-ਆਨ ਹਨ, ਅਸੀਂ ਮੈਕ ਲਈ ਦਫਤਰ ਚਲਾਉਂਦੇ ਹਾਂ ਜੋ ਮੈਕਰੋ ਨੂੰ ਸਮਰਥਨ ਨਹੀਂ ਦੇਵੇਗਾ. ਇਸ ਦੀ ਬਜਾਏ, ਅਸੀਂ ਸਹਾਇਤਾ ਲਈ ਸਿੱਧੇ ਫਾਰਮੂਲੇ ਭਾਲਦੇ ਹਾਂ. ਮੈਂ ਸੋਚਿਆ ਕਿ ਮੈਂ ਉਨ੍ਹਾਂ ਵਿੱਚੋਂ ਕੁਝ ਇੱਥੇ ਸਾਂਝਾ ਕਰਾਂਗਾ ਤਾਂ ਜੋ ਦੂਸਰੇ ਉਨ੍ਹਾਂ ਦੀ ਵਰਤੋਂ ਕਰ ਸਕਣ.

ਗੈਰ-ਸੰਖਿਆਤਮਕ ਅੱਖਰ ਹਟਾਓ

ਸਿਸਟਮ ਲਈ ਅਕਸਰ ਫੋਨ ਨੰਬਰਾਂ ਨੂੰ ਦੇਸ਼ ਕੋਡ ਦੇ ਨਾਲ, ਇੱਕ 11-ਅੰਕ ਦੇ ਫਾਰਮੂਲੇ ਵਿੱਚ ਪਾਉਣ ਦੀ ਜ਼ਰੂਰਤ ਹੁੰਦੀ ਹੈ ਅਤੇ ਕੋਈ ਵਿਰਾਮ ਚਿੰਨ੍ਹ ਨਹੀਂ ਹੁੰਦਾ. ਹਾਲਾਂਕਿ, ਲੋਕ ਅਕਸਰ ਇਸ ਦੀ ਬਜਾਏ ਡੈਸ਼ਾਂ ਅਤੇ ਪੀਰੀਅਡਜ਼ ਨਾਲ ਇਸ ਡੇਟਾ ਨੂੰ ਦਾਖਲ ਕਰਦੇ ਹਨ. ਲਈ ਇੱਕ ਵਧੀਆ ਫਾਰਮੂਲਾ ਹੈ ਸਾਰੇ ਗ਼ੈਰ-ਅੰਕੀ ਅੱਖਰ ਹਟਾਉਣੇ ਐਕਸਲ ਵਿੱਚ. ਫਾਰਮੂਲਾ ਸੈੱਲ ਏ 2 ਵਿਚਲੇ ਅੰਕੜਿਆਂ ਦੀ ਸਮੀਖਿਆ ਕਰਦਾ ਹੈ:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

ਹੁਣ ਤੁਸੀਂ ਨਤੀਜੇ ਵਜੋਂ ਆਉਣ ਵਾਲੇ ਕਾਲਮ ਦੀ ਨਕਲ ਕਰ ਸਕਦੇ ਹੋ ਅਤੇ ਇਸਤੇਮਾਲ ਕਰ ਸਕਦੇ ਹੋ ਸੰਪਾਦਿਤ ਕਰੋ> ਮੁੱਲ ਚਿਪਕਾਓ ਸਹੀ ਫਾਰਮੈਟ ਕੀਤੇ ਨਤੀਜੇ ਦੇ ਨਾਲ ਡਾਟਾ ਉੱਤੇ ਲਿਖਣ ਲਈ.

ਇੱਕ OR ਨਾਲ ਕਈ ਖੇਤਰਾਂ ਦਾ ਮੁਲਾਂਕਣ ਕਰੋ

ਅਸੀਂ ਅਕਸਰ ਇੱਕ ਆਯਾਤ ਤੋਂ ਅਧੂਰੇ ਰਿਕਾਰਡਾਂ ਨੂੰ ਸਾਫ ਕਰਦੇ ਹਾਂ. ਉਪਭੋਗਤਾ ਇਹ ਮਹਿਸੂਸ ਨਹੀਂ ਕਰਦੇ ਕਿ ਤੁਹਾਨੂੰ ਹਮੇਸ਼ਾਂ ਗੁੰਝਲਦਾਰ ਰਚਨਾਤਮਕ ਫਾਰਮੂਲੇ ਲਿਖਣ ਦੀ ਜ਼ਰੂਰਤ ਨਹੀਂ ਹੁੰਦੀ ਅਤੇ ਇਸ ਦੀ ਬਜਾਏ ਤੁਸੀਂ ਇੱਕ ਓ ਆਰ ਬਿਆਨ ਲਿਖ ਸਕਦੇ ਹੋ. ਹੇਠਾਂ ਦਿੱਤੀ ਇਸ ਉਦਾਹਰਣ ਵਿੱਚ, ਮੈਂ ਗੁੰਮ ਹੋਏ ਡੇਟਾ ਲਈ ਏ 2, ਬੀ 2, ਸੀ 2, ਡੀ 2, ਜਾਂ ਈ 2 ਦੀ ਜਾਂਚ ਕਰਨਾ ਚਾਹੁੰਦਾ ਹਾਂ. ਜੇ ਕੋਈ ਡੇਟਾ ਗੁੰਮ ਰਿਹਾ ਹੈ, ਤਾਂ ਮੈਂ 0 ਵਾਪਸ ਕਰਨ ਜਾ ਰਿਹਾ ਹਾਂ, ਨਹੀਂ ਤਾਂ ਇਕ 1. ਇਹ ਮੈਨੂੰ ਡੇਟਾ ਨੂੰ ਕ੍ਰਮਬੱਧ ਕਰਨ ਅਤੇ ਅਧੂਰੇ ਹੋਏ ਰਿਕਾਰਡਾਂ ਨੂੰ ਮਿਟਾਉਣ ਦੀ ਆਗਿਆ ਦੇਵੇਗਾ.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

ਟ੍ਰਿਮ ਅਤੇ ਕਨੈਕਟੇਨੇਟ ਫੀਲਡ

ਜੇ ਤੁਹਾਡੇ ਡੇਟਾ ਵਿੱਚ ਪਹਿਲਾਂ ਅਤੇ ਆਖਰੀ ਨਾਮ ਖੇਤਰ ਹਨ, ਪਰ ਤੁਹਾਡੇ ਆਯਾਤ ਵਿੱਚ ਪੂਰਾ ਨਾਮ ਖੇਤਰ ਹੈ, ਤਾਂ ਤੁਸੀਂ ਐਕਸਲ ਫੰਕਸ਼ਨ ਕਨਕੈਟੇਟ ਵਿੱਚ ਬਿਲਟ ਦੀ ਵਰਤੋਂ ਨਾਲ ਸਾਫ਼-ਸੁਥਰੇ ਰੂਪ ਵਿੱਚ ਇਕੱਠੇ ਕਰ ਸਕਦੇ ਹੋ, ਪਰ ਇਸ ਤੋਂ ਪਹਿਲਾਂ ਜਾਂ ਬਾਅਦ ਵਿੱਚ ਕਿਸੇ ਵੀ ਖਾਲੀ ਥਾਂ ਨੂੰ ਹਟਾਉਣ ਲਈ ਟਰਾਈਮ ਦੀ ਵਰਤੋਂ ਕਰਨਾ ਨਿਸ਼ਚਤ ਕਰੋ. ਟੈਕਸਟ. ਅਸੀਂ ਪੂਰੇ ਖੇਤਰ ਨੂੰ ਟਰਾਈਮ ਨਾਲ ਲਪੇਟ ਲੈਂਦੇ ਹਾਂ ਜੇ ਕਿਸੇ ਇੱਕ ਖੇਤਰ ਵਿੱਚ ਡੇਟਾ ਨਹੀਂ ਹੁੰਦਾ:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

ਵੈਧ ਈਮੇਲ ਪਤਾ ਦੀ ਜਾਂਚ ਕਰੋ

ਇੱਕ ਪਰੈਟੀ ਸਰਲ ਫਾਰਮੂਲਾ ਜੋ ਕਿ ਦੋਵਾਂ ਨੂੰ @ ਅਤੇ. ਇੱਕ ਈਮੇਲ ਪਤਾ ਵਿੱਚ:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

ਪਹਿਲੇ ਅਤੇ ਆਖਰੀ ਨਾਮ ਕੱ Extੋ

ਕਈ ਵਾਰ, ਸਮੱਸਿਆ ਇਸ ਦੇ ਉਲਟ ਹੈ. ਤੁਹਾਡੇ ਡੇਟਾ ਵਿੱਚ ਪੂਰਾ ਨਾਮ ਖੇਤਰ ਹੈ ਪਰ ਤੁਹਾਨੂੰ ਪਹਿਲੇ ਅਤੇ ਆਖਰੀ ਨਾਮਾਂ ਨੂੰ ਪਾਰਸ ਕਰਨ ਦੀ ਜ਼ਰੂਰਤ ਹੈ. ਇਹ ਫਾਰਮੂਲੇ ਪਹਿਲੇ ਅਤੇ ਆਖਰੀ ਨਾਮ ਦੇ ਵਿਚਕਾਰ ਜਗ੍ਹਾ ਦੀ ਭਾਲ ਕਰਦੇ ਹਨ ਅਤੇ ਜਿੱਥੇ ਵੀ ਜ਼ਰੂਰੀ ਹੋਵੇ ਟੈਕਸਟ ਫੜੋ. ਆਈ ਟੀ ਇਹ ਵੀ ਸੰਭਾਲਦਾ ਹੈ ਜੇ ਕੋਈ ਆਖਰੀ ਨਾਮ ਨਹੀਂ ਹੈ ਜਾਂ ਏ 2 ਵਿਚ ਖਾਲੀ ਐਂਟਰੀ ਹੈ.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

ਅਤੇ ਆਖਰੀ ਨਾਮ:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

ਪਾਤਰਾਂ ਦੀ ਗਿਣਤੀ ਸੀਮਿਤ ਕਰੋ ਅਤੇ ਸ਼ਾਮਲ ਕਰੋ ...

ਕੀ ਤੁਸੀਂ ਕਦੇ ਆਪਣੇ ਮੈਟਾ ਵਰਣਨ ਨੂੰ ਸਾਫ ਕਰਨਾ ਚਾਹੁੰਦੇ ਹੋ? ਜੇ ਤੁਸੀਂ ਸਮੱਗਰੀ ਨੂੰ ਐਕਸਲ ਵਿਚ ਖਿੱਚਣਾ ਚਾਹੁੰਦੇ ਹੋ ਅਤੇ ਫਿਰ ਮੈਟਾ ਵਰਣਨ ਦੇ ਖੇਤਰ ਵਿਚ ਵਰਤੋਂ ਲਈ ਸਮੱਗਰੀ ਨੂੰ ਟ੍ਰਿਮ ਕਰਨਾ ਚਾਹੁੰਦੇ ਹੋ (150 ਤੋਂ 160 ਅੱਖਰ), ਤੁਸੀਂ ਇਸ ਫਾਰਮੂਲੇ ਦੀ ਵਰਤੋਂ ਕਰਕੇ ਇਹ ਕਰ ਸਕਦੇ ਹੋ ਮੇਰੀ ਸਪਾਟ. ਇਹ ਇੱਕ ਜਗ੍ਹਾ ਤੇ ਵੇਰਵੇ ਨੂੰ ਸਾਫ ਤੌਰ ਤੇ ਤੋੜਦਾ ਹੈ ਅਤੇ ਫਿਰ…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

ਬੇਸ਼ਕ, ਇਹ ਵਿਆਪਕ ਹੋਣ ਲਈ ਨਹੀਂ ਹਨ ... ਸਿਰਫ ਇੱਕ ਛਾਲ ਮਾਰਨ ਵਿੱਚ ਤੁਹਾਡੀ ਸਹਾਇਤਾ ਲਈ ਸਿਰਫ ਕੁਝ ਤੁਰੰਤ ਫਾਰਮੂਲੇ! ਤੁਸੀਂ ਕਿਹੜੇ ਹੋਰ ਫਾਰਮੂਲੇ ਵਰਤ ਰਹੇ ਹੋ? ਉਹਨਾਂ ਨੂੰ ਟਿੱਪਣੀਆਂ ਵਿੱਚ ਸ਼ਾਮਲ ਕਰੋ ਅਤੇ ਮੈਂ ਇਸ ਲੇਖ ਨੂੰ ਅਪਡੇਟ ਕਰਦੇ ਹੋਏ ਤੁਹਾਨੂੰ ਕ੍ਰੈਡਿਟ ਦੇਵਾਂਗਾ.

ਤੁਹਾਨੂੰ ਕੀ ਲੱਗਦਾ ਹੈ?

ਇਹ ਸਾਈਟ ਸਪੈਮ ਨੂੰ ਘੱਟ ਕਰਨ ਲਈ ਅਕਕੀਮੈਟ ਵਰਤਦੀ ਹੈ. ਜਾਣੋ ਕਿ ਤੁਹਾਡੇ ਟਿੱਪਣੀ ਡੇਟਾ ਦੀ ਪ੍ਰਕਿਰਿਆ ਕਿਵੇਂ ਕੀਤੀ ਜਾਂਦੀ ਹੈ.