Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
#
How to split cell containing FRACTION into two cells including Numerator and denominator

- Home
- Microsoft 365
- Excel
- How to split cell containing FRACTION into two cells including Numerator and denominator

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2022 10:11 PM

Hi everyone here

If I have 4 cells containing the following fractions as example, my question is about the formula that can be applied for each for these cells to get two cells including **Numerator and denominator** for each fraction?**1/80**

**2/195**

**7/3744**

**7/8**

Kind regards

Labels:

20 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2022 10:20 PM

@ESAM_HASHIM Please see attached.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20 2022 01:17 AM - edited Apr 20 2022 01:18 AM

Thank you very much @Riny_van_Eekelen

I can conclude from your file the following critical points to apply the two formula correctly as shown by replied file that attached here:

1, The first step is to change Format Cells in all empty cells to be Text format

2. Then I must inter all required fractions MANUALLY because (as concluded) Excel cannot accept all options in copy pest at all

From all above points please, I need to verify my idea

Warm Regards

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20 2022 01:57 AM

@ESAM_HASHIM But you can copy and paste your list of fractions. First format the column as Text, and then paste special as text. That's how I did in in my example.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20 2022 07:09 AM

Please can you tell me how to PASTE SPECIAL as text to make the formula possible, because I didn't find any way rather than inter all required fractions MANUALLY to apply the specified formulas, you can copy the example sheet to a new sheet to be more understand your idea

Much apricated.

Much apricated.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20 2022 09:08 AM

@ESAM_HASHIM Not sure I understand your question. But in order to help you further, I'd need to have some more info. Where do the fractions come from? I understood that you already had them in a list in Excel and needed to split them. If not, pease clarify.

I just copied the fractions from this forum page into my own Excel and needed to use paste special into a text column, but perhaps you don't have to do that at all.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20 2022 12:26 PM - edited Apr 23 2022 01:50 AM

Thank you @Riny_van_Eekelen so much for your generosity

Please let me to explain the steps that applied by going to My_Worksheet file

1. Firstly, I pasted the Column A from another worksheets after changing the format cell to view fractions

2. I got the values shown in B column by 2 equations shown in the file

3. I have repeated values shown in column E **MANUALLY** from B column to be able to apply the two formulas in columns F & G

My question was are there another way rather than r**epeat values manually** in column E to be able to split all cells as shown in columns F & G

best response confirmed by
ESAM_HASHIM* (Brass Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20 2022 01:09 PM

SolutionTry this.

`=TRANSPOSE(FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(TEXT(B8,"# ????/????"),"/","#"),"#","</z><z>")&"</z></y>","//z"))`

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20 2022 01:52 PM - edited Apr 20 2022 01:53 PM

Thank you so much @Detlef_Lewin

I highly appreciate the info. This will help me enormously.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20 2022 03:15 PM

Using the latest version of 365, two separate columns might be

```
= TEXTBEFORE(TEXT(values,"????/????"),"/")
= TEXTAFTER(TEXT(values,"????/????"),"/")
```

or to combine the two columns into a single 2D array

```
= LET(
fraction, TEXT(values,"????/????"),
numerator, TEXTBEFORE(fraction,"/"),
denominator, TEXTAFTER(fraction,"/"),
CHOOSE({1,2},numerator,denominator)
)
```

The original values were numbers rather than text and the number format uses improper fractions.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 21 2022 11:54 PM - edited Apr 21 2022 11:56 PM

@Peter Bartholomew

Thank you for your trial to help me but I would like to inform you that the formula provided by @Detlef_Lewin is the most practical for my situation

I try to apply your formulas in My_Worksheet file but without any positive results, that's may be due to I am not expert in using those formulas you used in your example (fractions.xlsx)

Kind Regards

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 22 2022 07:17 AM

I like @Riny_van_Eekelen 's solution best. It can be spilled with a slight modification.

=LET(e,E8,CHOOSE({1,2},LEFT(e,FIND("/",e)-1),RIGHT(e,LEN(e)-FIND("/",e))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 22 2022 01:55 PM

Don't feel bad about it. You have to be a 365 user, signed up to the beta channel, for those particular formulas to work. I post them because exploring new functionality interests me and in order to seed the idea that tomorrow's spreadsheet solutions may be very different from yesterday's.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 23 2022 03:05 AM

I feel that there are the more covering info about my request to be as a best reference

you are most welcome, don't worry about my feeling

Have a nice day!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 18 2023 11:40 AM

Thank you Peter!!!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 10 2023 07:06 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 10 2023 07:49 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 10 2023 08:57 AM - edited Nov 10 2023 09:01 AM

I found it, thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 10 2023 02:33 PM

Looking back, I wonder why I selected CHOOSE to combine numerators and denominators into an array.

```
= LET(
fraction, TEXT(values,"????/????"),
numerator, TEXTBEFORE(fraction,"/"),
denominator, TEXTAFTER(fraction,"/"),
VALUE(HSTACK(numerator,denominator))
)
```

Of course, had it not been for Microsoft's dreadful decision not to support nested arrays / arrays of arrays, life would be a lot simpler. Unfortunately

```
= LET(
fraction, TEXT(values,"????/????"),
TEXTSPLIT(fraction,"/")
)
```

returns only the numerators. A trick to return both numerator and denominators is to join the value array before splitting it two ways

```
= LET(
fraction, TEXT(values,"????/????"),
string, TEXTJOIN("\",,fraction),
VALUE(TEXTSPLIT(string,"/","\"))
)
```

The sad fact is that the majority of formulas I now write contain obscure workarounds for the 'array of arrays' problem.

This is unfortunate because I would __define__ a modern spreadsheet as an environment in which __arrays of arrays__ may be manipulated to facilitate calculation. So why not do it properly? Excel with dynamic arrays is so nearly there in terms of functionality. The user interface needs further development to support testing and audit but that can evolve over time.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 10 2023 09:10 PM - edited Nov 11 2023 02:26 AM

@Peter Bartholomew I hear you! As mentioned, the TEXTSPLIT function is not capable of spilling its results with an array of values, unless implementing some sort of workaround. TEXTJOIN works but is limited to 32,767 characters. In this case, with each text string containing 9 characters + 1 delimiter, if the dataset contained more than 3,276 rows of data, TEXTJOIN would fail with the #CALC! error.

The only other workaround I've seen for creating an array of arrays is to use the REDUCE function with VSTACK. For example:

```
=REDUCE({"Numerator","Denominator"}, A2:A10000, LAMBDA(v,n,
VSTACK(v, --TEXTSPLIT(TEXT(n, "????/????"), "/"))))
```

However, this method performs very poorly with large datasets, due to its iterative nature and repeated use of VSTACK for each row in the range. If the dataset is large enough, it may even cause Excel to crash.

Your first example, though, using TEXTBEFORE and TEXTAFTER with HSTACK is lightning-fast in comparison when used with large datasets, and is definitely the way to go. My take would be to use something like this:

```
=LET(
arr, TEXT(A2:A10000, "????/????"),
--HSTACK(TEXTBEFORE(arr, "/"), TEXTAFTER(arr, "/"))
)
```

...which is basically the same as what you've done, but with a double-negative to convert the text to numbers, rather that the VALUE function. Cheers! :)

1 best response

Accepted Solutions

best response confirmed by
ESAM_HASHIM* (Brass Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20 2022 01:09 PM

SolutionTry this.

`=TRANSPOSE(FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(TEXT(B8,"# ????/????"),"/","#"),"#","</z><z>")&"</z></y>","//z"))`