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 Excel
- 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:

14 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* (Occasional 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!!!