Extracting specific word/number from a cell

[melbin]

Hi there,

Can anyone help me to pull the code number from a cell to the adjacent cell.

The code has 10 digits and it starts with "7". Attached please find the data.

[PeteABC123]

Attachment please.

[PeteABC123]

Place this into F3:

Please Login or Register  to view this content.

Copy down.

Pete

[melbin]

Here you go..

[melbin]

Thank you so much, Pete... Its working.

[PeteABC123]

Anytime.

[kenkie]

is that always start with "700" or it can be other numbers aside from "0" after the "7"?

Please Login or Register  to view this content.

[FDibbins]

Pretty much the same as kenkie...
=MID(C3,SEARCH("70",C3),10)

[jtakw]

Hi,

If you're using the formula in Post #3, you should change the 11 to 10, as it is, your result will contain a Trailing Space, which will be problematic if you're doing further comparisons with the results.

If your Code number Always starts with either 700 or 70, then use either formula from Post # 7 or #8.

But if the Only certainty is it Always starts with 7 (i.e. 7123456789), then use this:

Please Login or Register  to view this content.

See attached.

[PeteABC123]

jtakw: I sit corrected. 10 would be the magic number. Thanks for the checkup.
Pete

[melbin]

Hi , I need to lookup the date from Column C to Column G. The problem i face is some of the items in Column F and A are repeated and its amount are different. So if i simply apply vlookup, excel will not pick the correct amount for the items which are repeated.

For eg. ERRFVA111818-1 is repeated three times (line# 3,4 & 5) and the amount for each line are different($504, $1008 and $750). When i apply vlookup, excel will not pick all the three values/amount. it will only pick $504.

[Bo_Ry]

Vlookup can't be used for a duplicate search table, it only finds the first value for the exact match.

The simple one is G3
=C3

or more complex

=INDEX($C$3:$C$31,AGGREGATE(15,6,$A$3:$A$31/(F3=$B$3:$B$31),COUNTIF(F$3:F3,F3)))

[melbin]

Hi Bo,

Thank you so much. Its working. Appreciate your help.

[melbin]

Hi BO,

Sorry for the confusion.

This formula won't work if the data in column B and F are not in the same order. Could you please help me to find a formula if the data are not in the same order. Please find the attachment

[Bo_Ry]

Please try

G3
=INDEX($C:$C,AGGREGATE(15,6,ROW($B$3:$B$31)/(F3=$B$3:$B$31),COUNTIF(F$3:F3,F3)))

[melbin]

Thank you, Bo. Its working.
Once again thank you for your valuable time.

[melbin]

Thank you so much, Bo. Its working. That is what i exactly want.

Once again thank you for your valuable time.