Imagine this: you receive raw data for analysis and find out that numbers are mixed with text in one column. In most cases, it will certainly be more convenient to have them in separate columns for closer examination. Continue reading
by Svetlana Cheusheva, updated on
Imagine this: you receive raw data for analysis and find out that numbers are mixed with text in one column. In most cases, it will certainly be more convenient to have them in separate columns for closer examination. Continue reading
Comments page 2. Total comments: 54
Dear Alexander Trifuntov cordial greetings
I am working with a series of codes as follows
100001-PTR012
100001-PV012
I need to extract the non-numeric characters located after the hyphen (-), for which I have used the following formula:
=MID(A1,FIND("-",A1)+1,FIND("0",A1,FIND("-",A1))-FIND("-",A1)-1)
So far everything works
The problem arises when in the code, after the hyphen, the numeric characters begin with a non-zero (0) character, for example:
100001-PTR912
100001-PV512
100001-PVEW71204
What would be the best way to obtain only the non-numeric characters located after the hyphen, when they are preceded by non-zero numbers?
I appreciate your valuable collaboration.
Hello!
If I got you right, the formula below will help you with your task:
=LEFT(MID(A1,SEARCH("-",A1)+1,50), MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0}, MID(A1,SEARCH("-",A1)+1,50),1),10000))-1)
Use the LEFT function to start your search at the character following the "-".
Dear thank you very much for your help. It works correctly, a big hug!
Hi, I'm using the RemoveText custom function but it doesn't preserve decimals. For example, 950.43 becomes 95043. Is there a way to save the decimals?
Hello!
The comma is considered a character, not a number. Therefore, it must be added to the list of symbols that are not removed.
VBA code 2:
.Pattern = "[^0-9\,]"
You can also use regular expressions to remove specific characters from text. Here is a detailed instruction: Excel Regex to remove certain characters or text from strings.
In the RegExpReplace function, use the pattern "[^0-9\,]"
Thanks! That worked perfectly!
Hello,
It's the perfect solution. How to make an empty cell for the blank cell? Since we drag the formula down, the empty cell grabs some wired string and special character.
Thank you!
Hi Evan,
Nest your formula in the IF function like this:
=IF(A2="", "", your_formula)
Where A2 is the cell with the original data.
Hi Svetlana,
This is such a cool example; thank you for providing it. In my data I am trying to pull out numeric value from my text which is listed for example as 70 MPH. There could be other numeric values after the MPH but I dont want those. Can this formula be adjuster to only pull the numberic value before MPH? Thanks!
Hi Jodi,
If you are looking for a universal solution that can pull number before any text, please see How to extract number from the beginning of text string.
Hello!
Try to substitute this expression in the formula instead of A2:
LEFT(A2,SEARCH("MPH",A2)-1)
I hope it’ll be helpful.
Hi Svetlana,
what is the software you use to highlight your images in all your articles?
Kind regards
Hi Sukumar,
It's FastStone Capture.
Thank you
Regards