Hi, really liked this, worked really well. Just want to say as well, loved the way you went through it. Actually learned a little bit about what each bit of the formula does, rather than spoon feeding just the formula. Makes it easier to tailor it to my dataset and what I want to do. Thanks. Question: If I have a combination of letters, numbers and commas, is there a way or ignoring the numbers AND the commas in the one formula? My only idea is to use the formula in this video and just do a find/replace - commas for blanks. This is fine but would be easier if in a formula format as I update the data regularly.
Hi there, thanks for the message. I'm happy that you found the video useful. Find and replace is a great tool to use to remove the commas. However, there is a way to modify the formula. See below, using my formula from the video (indicated by the underline), you could wrap it with a SUBSTITUTE function: =substitute(___________,",","") **don't forget to remove the original = sign
Why would microsoft not just offer that as a default functionality rather than having to type that complicated function out? I can imagine this is a regularly required effort.
What if my data is like: Spain_50012 France4567 1134_Portugal I want to extract the country name alone. how to do that with a single formula which applies to all different types ?
I would like to extract the Numbers with special characters together, For Ex- " Inv. 1234-01, 12345-09 Submittal.msg " ,,, I want Only "1234-01, 12345-09"
Hi, that's an interesting one. If all of them start with 'Inv. ' then you might be able to use =textafter(A1,"Inv. ",1)... A1 is an example of the cell reference.
Fantastic way of separating texts and numbers!! Thanks for the lesson!!
Excellent, glad it was helpful
Hi, really liked this, worked really well. Just want to say as well, loved the way you went through it. Actually learned a little bit about what each bit of the formula does, rather than spoon feeding just the formula. Makes it easier to tailor it to my dataset and what I want to do. Thanks.
Question: If I have a combination of letters, numbers and commas, is there a way or ignoring the numbers AND the commas in the one formula? My only idea is to use the formula in this video and just do a find/replace - commas for blanks. This is fine but would be easier if in a formula format as I update the data regularly.
Hi there, thanks for the message. I'm happy that you found the video useful.
Find and replace is a great tool to use to remove the commas. However, there is a way to modify the formula.
See below, using my formula from the video (indicated by the underline), you could wrap it with a SUBSTITUTE function:
=substitute(___________,",","")
**don't forget to remove the original = sign
How do you scale this to numerous rows? I need to apply this to thousands of rows at a time.
thank you so much for such a way of explaining, super helpful.
Excellent, glad it was helpful.
Why would microsoft not just offer that as a default functionality rather than having to type that complicated function out?
I can imagine this is a regularly required effort.
Haha, yeah agree. This and many other formula.
How can I extract below text 100000 Phoenix Hill LLC distribution
What if my data is like:
Spain_50012
France4567
1134_Portugal
I want to extract the country name alone. how to do that with a single formula which applies to all different types ?
Hey there, you could wrap my formula with =substitute(myformula,"_",,"")
and subscribed.🙂
Wooo! Awesome.
I would like to extract the Numbers with special characters together,
For Ex- " Inv. 1234-01, 12345-09 Submittal.msg " ,,, I want Only "1234-01, 12345-09"
Hi, that's an interesting one. If all of them start with 'Inv. ' then you might be able to use =textafter(A1,"Inv. ",1)... A1 is an example of the cell reference.