How to extract characters or digits from cell content

There is more than one way to get what you want. Here is one:

Your 111.222.333 numbers are in column A

You will use four columns (B-E) to separate them.

B =LEFT(A,FIND(".",A)-1)

C =RIGHT(A,LEN(A)-FIND(".",A))

D =LEFT(C,FIND(".",C)-1)

E =RIGHT(C,LEN(C)-FIND(".",C))

B is the 1's

D is the 2's

E is the 3's

Hide column C

Hi K'

Here are two formulas that will do the job with strings following the same pattern as your example.

C2: MID(B2,2,LEN(B2)−2)

C3: SUBSTITUTE(SUBSTITUTE(B3,"<",""),">","")

The first, using MID, removes the first and last characters from the string, with no attention paid to what characters these are.

The second uses nested supstitutions to replace the specific characters "<" and ">" with null strings ( "" ), effectively deleting htes character from the string, without regard to their positions.

Regards,

Barry

You guys definitely know your stuff!

I have a question...

How would you remove the "<" and the ">" when contained around an email?

For example... How do I remove the "<" and the ">" from the email <info@wilpanganiban.com> when in cell A

and in a new cell to have only info@wilpanganiban.com to appear in cell B?

Any takers?

Thanks in advance.

Pap,

You can use the LEN function to determine the length of the original string. It may take more than one use of FIND to extract a substring from the middle.

Jerry

Hi papa,

Here is a workaround, but it involves a word processor such as Pages or Word.

Copy your cells and paste into a blank word processor document.

In Pages, go to:

Menu > Edit > Find > Find...

Click on the Advanced button.

Type a full stop ('period' in AmericanSpeak) in the Find box.

In the Replace box, use the 'Insert' Pop-up menu to insert a Tab.

Replace All.

Copy and paste into blank cells in Numbers:

How can I grab/calculate just the "1" or "22" or "333" from that?

Grab what you want!

Regards,

Ian.

papalapapp wrote:

Hi Yellow, thanks for that. I didn't know that I could actually put a tab as a replacement item wich is pretty cool. Unfortunately for my current task I need to stay within Numbers because I work with lists of IDs which I have to process (they are keys for categories).

Jerrold, I have been trying around with LEN but I can't find out how to get the numbers to the right of the first ".".

It's a bit of a pain. I find that I can get there faster if I use some auxiliary columns so the individual expressions don't get so long. Here's an example:

The formulas are as follows:

First Period Position:

=FIND(".", A, 1)

First SubString:

=LEFT(A, FIND(".", A, 1)-1)

Second Period Position:

=FIND(".", A, FIND(".", A, 1)+1)

Middle SubString:

=MID(A, B+1, D-B-1)

Third SubString:

=RIGHT(A, LEN(A)-D)

Regards,

Jerry

You did give us both a 10. What a gentleman!

Regards,

Jerry

As Badunit says (and Ian and Jerry demonstrate), there's often more than one way to get what you want.

Here's one more, using no auxiliary columns:

B2: =LEFT(A,FIND(".",A)-1)

C2: =MID(A,FIND(".",A)+1,FIND(".",A,(FIND(".",A)+1))-FIND(".",A)-1)

D2: =RIGHT(A,LEN(A)-FIND(".",A,(FIND(".",A)+1)))

Interesting exercise. as can be seen in the last row, this solution also works with words (as do the others—they all treat what's in the source column as a text string; the actual contents of that string, except for the two periods, doesn't matter.

Regards,

Barry

Hello!

I’m trying to also extract something in a similar way. Out of this string: “2013-12-08” I’d like to return the “12“ (for December).

What would be the formula therefor, please, assuming the string is located in a cell called “B3”?

🙂

Using Barry's formula with some slight editing:

=MID(B3,FIND("-",B3)+1,FIND("-",B3,(FIND("-",B3)+1))−FIND("-",B3)−1)

And of course life is simpler if your data happens to be uniform length and format. In that happy case, you can just do:

=MID(B3,6,2), displaying Text, with the leading zero.

=MONTH(B3) can work too, displaying Number without the leading zero (unless you format it as Numeral System Base 10 Places 2, a trick learned from Jerry)

SG

Hey Badunit,

Thank you for your answer! Now it works!

I’d just like to mention one thing I encountered inbetween. I think it’s due to the fact I use OSX in German and started NUMBERS in English via an app called Language Switcher.

First, my formula looked like that:

I tried to compare my line (the upper one) with yours (the one beneath), but both of them brought the same syntax error message:

I thought the error would come from the hyphens (minus dashes) but at the end, it was due to the comma. Replacing the commas by semicolons fixes the issue and now returns the correct value:

Cool 🙂