This was the type of formular I was looking for. This was extremely helpful for my assignment as we were required to use a formular instead of a wizard tool. The use of the find function within the formular makes it flexible to deal with varied characters of text. Thanks so much!
Aside from the obvious functions instruction, I think some may miss the bigger lesson shared in this video... that spreadsheets are basically a calculator looking for 11-5=6... Thanks for reminding me. Hopefully, it'll make my own advancement into spreadsheets a bit easier moving forward.
there are a lot of different ways to separate data from cells and it can get very complicated depending on what you're doing. You can send me a message on here with an example of what you're trying to do and I can help you with that but without knowing what you need the data separated by, it will be hard to make a tutorial useful for you.
Hello, I have a cell with 4 spaces and I need the last name. The cell contains the string MR & MRS JOHN SMITH. I used this formula =RIGHT(A5,LEN(A5)-FIND(" ",A5,FIND(" ",A5,FIND(" ",A5,FIND(" ",A5)+1)))) since there are 4 spaces. I used your formula which worked great with 1 space and 2 spaces in the text strings but I applied the same formula using 1 find for each space and I get MRS JOHN SMITH no matter how many more or less FINDS I use. I know I'm missing something because I understand the formula. Thanks very much. By the way, your teaching style is excellent.
let me start by saying this is an excellent example. I'm trying to use this formula to solve a problem with a column pre-filled with about 200 addresses. If I understand how this formula works, it would only make sense if number of spaces were exactly the same. I'm trying to separate physical address, city, state and zip. Can you do an example with different addresses?
If you are having trouble understanding how Excel counts the chars within a given text, hit the insert ("INS" on my Toshiba) button. Excel counts the chars themselves, not necessarily chars that are located based on the number of the tiny space to the right or left of it (that you're caret/flashing cursor is on)
Could you please help me out with this. If I have a list. (Manager´s last names (2) and 2 names e/a. How can I extraxt the second name? What would the formula be? the other situation was Supervisor´s last names (2) and 2 names. Get the first last name for each supervisor. I used =mid(text, position in text,find( " ",text,position) and I got it, but this, haven´t been able to do it. Thanks
Hi Sir, I need your help. we have a uqique number in a cell. for example 1032. I have used formula for found out the mid of 2 number. result is 03. now I want to the same number is showing like 3rd, 2nd, 1st, 4th as per the unique no
In mid formula you have used +1with find in starting of the formulae =mid(a9,find(" ", a9)+1, How i am not getting because the first find whwn we use at that time we only write like find(" " ,a9,find(" ", a9)+1
Hi, Can you please assist me with the following if my data contains: John John East John West John What i want is the word John in a column. I use the combination of Left and Search functions however the result of the word John itself gives me #Value (Error). The data I'm working with is to much to change all those to John then. Your assistance will be much appreciated.
Any chance you can help me that's a step up from this example? I have one column used for comments but each comment is completely different in number of characters with spaces in between. So somewhere in the comment there is a check # (ex. Blah blah blah CH#12345 blah) that I would like extracted. So the check # is never in the same number of characters in the cell. I want to be able to find "CH#" and the first space after it so that i only capture the check # that is somewhere between random text.
ok...if you have 20 numbers as text in one column, how do you show if the 20 numbers have any 3 that your looking for..say you want to know if 02-26-76 is in the list of numbers? 01-02-08-12-26-48-49-74-76....what formula will tell us if this combination of numbers exist in the entire column of numbers...say there are 200 rows of 20 numbers, we are specifically looking to see if those numbers exist (regardless of order) in any of the rows...
Sir Thank you for your great tutorial. I will thankful to you If you help me further How can I separate Text and Number when both are in same column of different cells. like 11 (in cell A1) Abdul Rahaman(A2) 222 (in cell A4) Karishma Chauhan Mahan (A5) Where 11 is Abdul Rahaman's Number and so on. I want name and number in different column.
Depends, do you want it on a large set of data that is formatted the exact same way for each cell or what? You could use Text-to-Columns or the MID function. Ask this in our forum and you can post a sample set of data that shows any variations in it, as that's where the issues usually arise with this kind of operation. www.teachexcel.com/talk/microsoft-office?src=yt
Hi. could you help me with this issue? PART LIST OF.... # part number QTY 120QWEO1203 1 The list has been named "PART LIST OF..." which contains the entire part numbers of a product. Is there a way to look for the name of the list and display automatically the content of the 2 columns "#part Number, QTY"?. Salutes
Excellent tutorial! Does anyone have any clue how I can split this up? Monday 17:0000:00 Tuesday 17:0000:00 Wednesday 17:0000:00 Thursday 17:0002:00 Friday 17:0002:00 Saturday 17:0002:00 Sunday 17:0000:00 It all in one cell atm It should be: DAY | Time open | Time Close Any help would be much appreciated
hi ExcelisHell Fair value of plan assets first of year, $ 150000, $ 161,500 $ 195,500 Accrued benefit obligation ( ABO) for , 150,000, 289,000 363,300 Current service cost for year, 16,000,17,500 19,000 Can you change it in excel like Fair value of plan assets first of year, $ 150000, $ 161,500 $ 195,500 in different cell like 4 cell Thanks
This was the type of formular I was looking for. This was extremely helpful for my assignment as we were required to use a formular instead of a wizard tool. The use of the find function within the formular makes it flexible to deal with varied characters of text. Thanks so much!
1:35 - 1:53 is excellent teaching. Great job. Thank you.
You can also use delimiter. Go to data > split text to columns > select a separator like "space".
Aside from the obvious functions instruction, I think some may miss the bigger lesson shared in this video... that spreadsheets are basically a calculator looking for 11-5=6... Thanks for reminding me. Hopefully, it'll make my own advancement into spreadsheets a bit easier moving forward.
You are very welcome :)
thanks for speaking the language of the people! I finally understand this!
Really helpful! Thank you teacher. You saved me a lot of trouble. At first it was a bit complicated to grasp but I did it finally!
Thank you for this, really helpful for someone just starting out. I hope your day is blessed as you just did to mine 🙏🏽
Xb ngsshvnafdneygmteegwfvmeyedbxbsfcnsgxbsv cCzcxbag x xaffadhdhrwhdfwdgmvfsststdbafxbsgxnicnxucikdxifmfifjfinfif9jmrirofmr
there are a lot of different ways to separate data from cells and it can get very complicated depending on what you're doing. You can send me a message on here with an example of what you're trying to do and I can help you with that but without knowing what you need the data separated by, it will be hard to make a tutorial useful for you.
i am not able to use right formula ...i want to extract last word from the cell ...
This was very helpful....but if I have a column of data in which there are 2 different seperators, then what will be the formula
Hello, I have a cell with 4 spaces and I need the last name. The cell contains the string MR & MRS JOHN SMITH. I used this formula =RIGHT(A5,LEN(A5)-FIND(" ",A5,FIND(" ",A5,FIND(" ",A5,FIND(" ",A5)+1)))) since there are 4 spaces.
I used your formula which worked great with 1 space and 2 spaces in the text strings but I applied the same formula using 1 find for each space and I get MRS JOHN SMITH no matter how many more or less FINDS I use. I know I'm missing something because I understand the formula.
Thanks very much.
By the way, your teaching style is excellent.
I am searching last 2 years finally i found treasure
let me start by saying this is an excellent example. I'm trying to use this formula to solve a problem with a column pre-filled with about 200 addresses. If I understand how this formula works, it would only make sense if number of spaces were exactly the same. I'm trying to separate physical address, city, state and zip. Can you do an example with different addresses?
If you are having trouble understanding how Excel counts the chars within a given text, hit the insert ("INS" on my Toshiba) button. Excel counts the chars themselves, not necessarily chars that are located based on the number of the tiny space to the right or left of it (that you're caret/flashing cursor is on)
Thank you so much to help to solve my problem.
awesome tutorial thanks for putting useful tutorial your explanation is also awesome.
please put financial function tutorial video
thank you
God bless
Thank you! Just what I was looking for.
Thanks very helpful
THANKS!... FANTASTIC EXPLANATION!!!
many Thanks Mr. More than great
Could you please help me out with this. If I have a list. (Manager´s last names (2) and 2 names e/a. How can I extraxt the second name? What would the formula be? the other situation was Supervisor´s last names (2) and 2 names. Get the first last name for each supervisor. I used =mid(text, position in text,find( " ",text,position) and I got it, but this, haven´t been able to do it. Thanks
Way of teaching great.
very helpful. please post more!
Hi Sir, I need your help.
we have a uqique number in a cell. for example 1032. I have used formula for found out the mid of 2 number. result is 03. now I want to the same number is showing like 3rd, 2nd, 1st, 4th as per the unique no
Thank you very much for this! This helped me out a lot.
Glad to help)
Can you please help of 3 times space right function
Thanks very much for the explanation. However, I think you could have used "IFERROR" formula to the comma cases. Thanks anyway!
what is the formula to have cell AD be exactly like A9? want the name to be the same
@identity4free How do you want to generate the 4-letter name? Just message me on here and I can help you from there.
In mid formula you have used +1with find in starting of the formulae =mid(a9,find(" ", a9)+1,
How i am not getting because the first find whwn we use at that time we only write like find(" " ,a9,find(" ", a9)+1
Amazing Vid!
Like this we write fomula generally
Awesome sir.
I think you just saved everyone in my office from getting their head chewed off, THANK YEW
Hi,
Can you please assist me with the following if my data contains:
John
John East
John West
John
What i want is the word John in a column. I use the combination of Left and Search functions however the result of the word John itself gives me #Value (Error). The data I'm working with is to much to change all those to John then.
Your assistance will be much appreciated.
Very creative, but I think I'll just stick with Text to Columns.
Any chance you can help me that's a step up from this example? I have one column used for comments but each comment is completely different in number of characters with spaces in between. So somewhere in the comment there is a check # (ex. Blah blah blah CH#12345 blah) that I would like extracted. So the check # is never in the same number of characters in the cell. I want to be able to find "CH#" and the first space after it so that i only capture the check # that is somewhere between random text.
Thanks!
ok...if you have 20 numbers as text in one column, how do you show if the 20 numbers have any 3 that your looking for..say you want to know if 02-26-76 is in the list of numbers? 01-02-08-12-26-48-49-74-76....what formula will tell us if this combination of numbers exist in the entire column of numbers...say there are 200 rows of 20 numbers, we are specifically looking to see if those numbers exist (regardless of order) in any of the rows...
Please ask questions like this in our forum, where you can also provide a sample workbook. www.teachexcel.com/talk/microsoft-office?src=googleplus
Sir
Thank you for your great tutorial.
I will thankful to you If you help me further
How can I separate Text and Number when both are in same column of different cells.
like 11 (in cell A1)
Abdul Rahaman(A2)
222 (in cell A4)
Karishma Chauhan Mahan (A5)
Where 11 is Abdul Rahaman's Number and so on.
I want name and number in different column.
TG for text to columns ;)
idol..^^ thanks.
how to get output 0.55 from the following text,
plate xx mm x 0.55 mm
Depends, do you want it on a large set of data that is formatted the exact same way for each cell or what? You could use Text-to-Columns or the MID function. Ask this in our forum and you can post a sample set of data that shows any variations in it, as that's where the issues usually arise with this kind of operation. www.teachexcel.com/talk/microsoft-office?src=yt
how if there are 5 spaces or commas?
+mary rose romulo Use text to column
GOOD...
What if i have 4 Words like this ? JUF-E BRACE JOYOF NO.2
What do you want to do with them? Ask in our forum: www.teachexcel.com/talk/microsoft-office?src=yt
Hi. could you help me with this issue?
PART LIST OF....
# part number QTY
120QWEO1203 1
The list has been named "PART LIST OF..." which contains the entire part numbers of a product. Is there a way to look for the name of the list and display automatically the content of the 2 columns "#part Number, QTY"?.
Salutes
yes, by nesting the isnumber, find ,left function we can do , if you want me do that, just revert me
Excellent tutorial!
Does anyone have any clue how I can split this up?
Monday 17:0000:00 Tuesday 17:0000:00 Wednesday 17:0000:00 Thursday 17:0002:00 Friday 17:0002:00 Saturday 17:0002:00 Sunday 17:0000:00
It all in one cell atm
It should be:
DAY | Time open | Time Close
Any help would be much appreciated
hi ExcelisHell
Fair value of plan assets first of year, $ 150000, $ 161,500 $ 195,500
Accrued benefit obligation ( ABO) for , 150,000, 289,000 363,300
Current service cost for year, 16,000,17,500 19,000
Can you change it in excel like
Fair value of plan assets first of year, $ 150000, $ 161,500 $ 195,500
in different cell like 4 cell
Thanks
Hi Sir, do you have facebook messenger?
WHAT?!!!