You saved me time on googling an Excel formula on how to do this to 14 digit numbers that I will have to sort through daily. Thank you so much for sharing this.
@gaps190000
Жыл бұрын
You're a LEGEND! Thanks so much for this. Saved me so much time
@excelisfun
Жыл бұрын
Glad this helps!
@LeeDixon2
8 жыл бұрын
So simple yet so effective. I wish I saw this video in the morning today it would have saved me 35 mins at work.
@excelisfun
8 жыл бұрын
+Lee Dixon , Darn!! I wish that I had posted it yesterday so that it could have helped you with work!!!
@LeeDixon2
8 жыл бұрын
No matter I'm grateful for your videos whenever they arrive. They always help me out and I should be saying thank you more often.
@excelisfun
8 жыл бұрын
+Lee Dixon Cool! Thanks for the Thank Yous!!!
@at-excel
8 жыл бұрын
Hi Mike,thanks for sharing this idea.You can use Flash Fill as well. Excel recognizes the pattern.
@excelisfun
8 жыл бұрын
+Andreas Thehos , O, I love it! Flash Fill is truly amazing!!!!
@billnguyen5160
3 жыл бұрын
Thank you very much. Appreciate this trick, it helped me a lot.
@excelisfun
3 жыл бұрын
You are welcome !!!
@rrrprogram8667
5 жыл бұрын
Greatt.... This channel is never endingExcel
@excelisfun
5 жыл бұрын
Yes indeed, RRR, lots of fun : )
@WalkerB2730
Жыл бұрын
Bloody brilliant. So simple when you know
@JustShineCleaningServices
4 ай бұрын
It work for me thank you
@excelisfun
4 ай бұрын
Glad it helped!!
@GopalKumar-po9vw
8 жыл бұрын
Thanks Mike For Sharing very simple and amazing trick
@excelisfun
8 жыл бұрын
+Gopal Kumar You are welcome!!!
@BasicStudyClasses22
5 жыл бұрын
Thank you so much for this video sir so very helpful video
@animaldiseaseresearchinsti1780
Жыл бұрын
THANK YOU SO MUCH
@RizwanKhan-gi6fz
5 жыл бұрын
Really Great Job save the time...
@vidyasagar7712
3 жыл бұрын
Tq so much for giving better idea
@danielwoods5465
4 жыл бұрын
YOU. LEGEND.
@excelisfun
4 жыл бұрын
Thank you, sir : )
@davidyuen2448
2 жыл бұрын
Thank you for your video explanation. But, over the past 5 years I have been asking every expertise online for solution to add spaces between custom format alphanumeric. Take for example 123A4567 and 123A4B5C6. Is there a formula to custom it to 123 A4 567 and 123 A4 B5C6?
@dikekamundu
2 жыл бұрын
Thanks that was helpful but what if the length of the values are not all the same. E.g some 9 digits, some 10 digits?
@smackenzie1340
7 жыл бұрын
Great video but ca n the same be done to cell with numbers and text? like "1w34rt" to "1 w 3 4 r t" etc?
@lazalazarevic6192
8 жыл бұрын
excellent number formating trick
@excelisfun
8 жыл бұрын
+Laza Lazarevic Glad you like it!
@cathappytimes
3 жыл бұрын
Thank you
@excelisfun
3 жыл бұрын
You are welcome!
@bharatmatakijai5902
3 жыл бұрын
@@excelisfun how to change 1800 into 18:00
@excelisfun
3 жыл бұрын
@@bharatmatakijai5902 =REPLACE(1800,3,0,":")
@bharatmatakijai5902
3 жыл бұрын
@@excelisfun thank you sir you are very helpful
@shikhabiplow1077
3 жыл бұрын
A also want to put comma between them and pace both together at same time... Is it possible? I hope you have answer plz reply
@09shirish
4 жыл бұрын
Hi Sir, thanks for you vaulable guidance. I've a problem i.e. Credit Card is 9 digit no. if any formula or command which is recognized whether less than 8 or greater than 9 its make error, its means credit card digit will be selected only 9 digit
@ExcelStrategy
8 жыл бұрын
I vote for the formula all the life much more flexible !
@excelisfun
8 жыл бұрын
+ExcelStrategy Flex is good!
@kameshsharma5164
6 жыл бұрын
Hello Sir, You always provide very needful videos. Thanks for this help. Sir I have a question. Let's suppose we have a paragraph of 800 characters. we need 1 or 2 or 3 complete lines from start of the paragraph but the length of all characters is not more than 230-240 and also not less than 140- 150 characters. Please solve this problem Sir.
@kishanisrani4698
5 жыл бұрын
THANK YOU VERY MUCH SIR
@excelisfun
5 жыл бұрын
YOU ARE WELCOME, Kishan : )
@BillSzysz1
8 жыл бұрын
Nice Text trick :-) Thanks.
@excelisfun
8 жыл бұрын
+Bill Szysz You are welcome, O Masterful One!!! :)
@danielpelago5145
4 жыл бұрын
this video helps a lot.. but i am looking on how to add space and comma between each numbers in a single cell? like in a single cell i have 123456 to 1, 2, 3, 4, 5, 6?
@excelisfun
4 жыл бұрын
Try: Excel 2016: =TEXTJOIN(", ",,MID(G9,ROW(INDIRECT("1:"&LEN(G9))),1)) or Office 365: =TEXTJOIN(", ",,MID(G10,SEQUENCE(LEN(G10)),1)) or Before Excel 2016: =CONCATENATE(MID(G9,ROW(INDIRECT("1:"&LEN(G9))),1)&", ")
@danielpelago5145
4 жыл бұрын
@@excelisfunthanks sir!!
@excelisfun
4 жыл бұрын
@@danielpelago5145 Did it work? Which did you use?
@danielpelago5145
4 жыл бұрын
@@excelisfun this one: Before Excel 2016: =CONCATENATE(MID(G9,ROW(INDIRECT("1:"&LEN(G9))),1)&", ") thanks a lot sir!!
@excelisfun
4 жыл бұрын
@@danielpelago5145 Cool! I hope to make a video about this next week : )
@Simlaoui
8 жыл бұрын
awesome ; thank you for your sharing.
@excelisfun
8 жыл бұрын
+elhassan simlaoui You are welcome!
@masterof
8 жыл бұрын
Thank you!! As you mentioned, the Custom Number Formatting is only for the numbers, so numbers will work fine. If the cell contains mixing letters and numbers (for example, a software license key, a product model number, product serial number...etc), is still a way to add a space every few digits, while retaining the original values in the cell? Thanks a lot...!
@excelisfun
8 жыл бұрын
+rf05mjy I don't know how to do it with Number Formatting... :(
@masterof
8 жыл бұрын
I will continue use my old fashion way to do it: Add a helper column, use LEFT/MID/RIGHT () and insert space in between, until one day maybe Excel will have a built-in function for that....Thanks for your reply.
@killerhunk1
8 жыл бұрын
Awesome !!! 👍
@excelisfun
8 жыл бұрын
+killerhunk1 Glad you like it!
@SantoshBudakoti
8 жыл бұрын
Great Video.... Could you please make a video on Cohort Analysis in excel, if possible..
@excelisfun
8 жыл бұрын
+Santosh Budakoti I am sorry, but I do not know Cohort Analysis...
@tricitypropertyguru3682
Жыл бұрын
how to do the same but on google sheets. i want this same on google sheets pls help, any one know share the link of site or video thanks
@anpakkumar4567
6 жыл бұрын
Hi Mike....what if the phone number length differs as in here you have 9...i have 8 to 12 after country code...eg: +54 0000000000 ?
@kerzen4634
5 жыл бұрын
Hi there, I would like to add a space between each charcter, using a formula ( no VBA ). A1: TREE B1: T R E E Thanks
@excelisfun
4 жыл бұрын
Try: Excel 2016: =TEXTJOIN(", ",,MID(G9,ROW(INDIRECT("1:"&LEN(G9))),1)) or Office 365: =TEXTJOIN(", ",,MID(G10,SEQUENCE(LEN(G10)),1)) or Before Excel 2016: =CONCATENATE(MID(G9,ROW(INDIRECT("1:"&LEN(G9))),1)&", ")
@thisonedesign
4 жыл бұрын
Thank you for the video. How would I add multiple spaces EG: BK2004567 = BK 200 4567
@abuusman560
3 жыл бұрын
sir I have a problem I am using Arabic keyboard when i put date (25 March 2021) as 25-03-2021 it will be appear as 2021-03-25, please help in this regard
@Uvi3112
6 жыл бұрын
I love u man!
@shakhobiddinnakiev6767
2 жыл бұрын
it did not work, no matter which condition of zeros, it always split digits by 3 of them together, like 000 00 0000 output: 000 000 000 , always like this. How to fix that?
@ghazwanzinah2597
8 жыл бұрын
Hi Mike Can you learn me how I print symbol (IPA English)with shortcat key ( like ( æ , ŋ , ɹ
@excelisfun
8 жыл бұрын
+Ghazwan Elias I am sorry, but I don't know how to do that. Anyone else?
@AjaySharma-tz2xd
7 жыл бұрын
hi you formate a numbers trick are good but i want to formate different type of digits like 7 digits formate in 00 000 00, 9 digits 00 000 0000 its number are formate in one time not formate each 7 and 9 digits so solve qus
@bauglirm7036
4 жыл бұрын
hi , imust turn numbers like this "151151002" to "15.115.1002" . After second and fifth numbers i mest add dots. pls help !!!
@kyledeforest44
11 ай бұрын
How do I do this but with “:”
@bagussantosopartnerberbagi2249
7 жыл бұрын
how to make , to separate a thousand?
@nitinpalve4718
7 жыл бұрын
How to remove space after amount in excel
@kalpanaperumal8471
Жыл бұрын
Hey hi... 0000 0000 0000 0000 accepting formula but 3309 1001 7020 1977 but it converted and take last digit as 3309 1001 7020 1970.. What can I do?
@pyralidaepeace5774
2 жыл бұрын
👌🙏❤
@farhanawali4008
2 жыл бұрын
👍
@a.thomville
8 жыл бұрын
F'ing awesome!
@imranali-iy5wk
4 жыл бұрын
how to add space in first last or mid name like this ImranAli AliRazaQazmi ArhamMustafaJan AnsarMansoor GhulamShahidRabbani Plz solve this query
@mihirbandodkar9406
4 жыл бұрын
kzitem.info/news/bejne/o6uosp6Km52dd6A try if this works
@MuhammadIqbal-bn3dv
6 жыл бұрын
Hi Mike, thanks for your video. How to add space between text? abcdefg to a b c d e f g
@excelisfun
4 жыл бұрын
Try: Excel 2016: =TEXTJOIN(", ",,MID(G9,ROW(INDIRECT("1:"&LEN(G9))),1)) or Office 365: =TEXTJOIN(", ",,MID(G10,SEQUENCE(LEN(G10)),1)) or Before Excel 2016: =CONCATENATE(MID(G9,ROW(INDIRECT("1:"&LEN(G9))),1)&", ")
@amdslamdsl
6 жыл бұрын
How to add space in start of number, i mean like this, i have number 12345, i want to make that number to this: (space)12(space)345 Every responses are appreciated
@kamrulzoha9223
2 жыл бұрын
useless (its changing my last digit in 0 and actual it is 3
Пікірлер: 86