Excel automatically converting 7 digit CAS number to another number date

0 votes

Problem: I am attempting to deal with two lists. In columns A and B, there is one labelled "HYPHEN" and another labelled "CAS Number."

If a hyphen is present in column A, it is placed before the adjacent CAS number, which is then entered below, and so on until all hyphens and CAS numbers are present. Column C utilizes a formula that combines column A and column B and ranks them. To help illustrate this, I've included an image, and the formula to duplicate it is provided below.

CAS number being incorrectly converted to another number when Sorted (highlighted in red)

To replicate the issue: Columns A and B can have any data entered. To replicate the output of column C the formula is given below:

Formula for Column C: =FILTERXML(""&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:B26),",","")&"","//b")

(Formula provided by @Gary's Student on Stack Overflow)

Can someone please help me with this?

Sep 24, 2022 in Others by Kithuzzz
• 38,010 points
583 views

1 answer to this question.

0 votes

Looks like you could use:

enter image description here

The formula in D2:

=SUBSTITUTE(FILTERXML("<t><s>'"&TEXTJOIN("</s><s>'",,A2:B10)&"</s></t>","//s"),"'","")

Or:

=MID(FILTERXML("<t><s>'"&TEXTJOIN("</s><s>'",,A2:B10)&"</s></t>","//s"),2,99)
answered Sep 25, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Is there a way to produce a sum according to date/time stamp values of another column on excel?

Assuming the input data for the second ...READ MORE

answered Mar 25, 2023 in Others by narikkadan
• 63,420 points
385 views
0 votes
1 answer

Is there a function in excel to automatically calculate age using date of birth?

Try  =INT((YEARFRAC(TODAY(),B3,1)))  Where cell B3 contains a date like ...READ MORE

answered Mar 28, 2023 in Others by Kithuzzz
• 38,010 points
309 views
0 votes
1 answer

Excel Issue- custom format for Turkish currency

What about: #.##0,00 [$₺-tr-TR] Where #.##0,00 € would do for ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,420 points
1,087 views
0 votes
1 answer

Count Function for identifying the Unique values in Excel

If the 9th row is empty: =COUNTA(UNIQUE(TOCOL(F8:R10)))-1 If not ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,420 points
273 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,237 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,425 views
+1 vote
1 answer

Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,420 points
605 views
0 votes
1 answer

Stop Excel from automatically converting certain text values to dates

I've discovered that getting the desired result ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,420 points
314 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP