Share via

Extract numbers separated by / in a single cell

Anonymous
2022-03-27T02:01:25+00:00

I have a column of data that is separated by /

This data do not contain dates. They are numbers separated by the character: /.

For example, Cells C9:C15 contain this input:

1/0/14

12/2/1

1/3/07

1/5/07

0/1/8

9/4/11

0/6/3

I want a formula Cells L9:L15 to return this:
1

12

1

1

0

9

0

I want a formula in Cells M9:M15 to return this:

0

2

3

5

1

4

6

I want a formula in Cells N9:N15 to return this:

14

1

7

7

8

11

3

In Cell L9, this formula sometimes works to get the first number, but it doesn't always work. Sometimes it returns a random 5-digit number, don't know why. It also does not help me get the second number, or the third number: =TRIM(MID(SUBSTITUTE($C9,"/",REPT(" ",17)),COLUMNS($L9:L200)*17-(17-1),17))

Thanks for any help!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2022-03-28T08:40:44+00:00

    Image

    Single formula - enter below in cell L2 and copy down and to the right:

    =TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",LEN($A2))), (COLUMN(A$1)-1)*LEN($A2)+1, LEN($A2)))

    In case you want "7" in cells N4 and N5 instead of "07", simple precede the formula with double negation "--".

    Also to simplify, you may substitute "LEN($A2)" by "255" - LEN appears thrice in the formula.

    Regards

    Amit Tandon

    http://www.excelanytime.com/

    2 people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2022-03-27T03:04:49+00:00

    Hi

    My name is André. I am an independent consultant.

    are these data dates?

    If not, you can try the example on print

    But if they are dates, you can try to use the functions to extract dates

    =DATE()

    Answer here so I can continue helping you.

    André.

    1 person found this answer helpful.
    0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2022-03-27T23:15:32+00:00

    Hi,

    Use Data > Text To Columns with / as a separator.

    0 comments
  2. Anonymous
    2022-03-27T03:18:26+00:00

    Re: extract numbers

    The ANYPART custom function can do that.

    Image

     =ANYPART( vText, vPiece, vDivider1, vDivider2)

 =ANYPART( $B6, COLUMNS($A:A), "/")

  Fill across and down.

A space is used for the dividers if both are omitted.

Only one vDivider is needed and the dividers can be different values.

    Its part of the free Custom_Functions Excel Add-in.

    Which contains over 20 new functions that work exactly

    like the built-in functions.

    Download from OneDrive... https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

    '---
    Nothing Left to Lose

    0 comments
  3. Anonymous
    2022-03-27T03:07:25+00:00

    =day =month =year Example

    0 comments