Extracting text from a cell

[Partsguy100]

I have a long description in my item master and want to extract the first string after the number 205 in the description. I understand the logic by following other examples but can't get it to work.


Here is the long description that is essentially a big text box. I formatted the text to at least have the numbers on the left side, but even that is hit and miss as the wrap text formatting must be selected.

000 1113567 EA N 060 WASHER PLAIN 33/64 IN ID X 15/16 IN OD X 1/16 IN THK 205 6101229 GM DIESEL I would like to extract the number 6101229 BONUS: how can I format the cell the wrap around the numbers? Please help!!

<tbody>
</tbody>

 

[Rick Rothstein]

Is the number you want to find always a 7-digit number? If so...

=MID(A1,FIND(CHAR(10)&"205 ",A1)+5,7)

I don't understand your BONUS question. Are you saying the text you posted is not your actual text?

 

[sandy666]

With PowerQuery and your example you can try:

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Text Between Delimiters" = Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "205 ", " "), type text)
in
    #"Inserted Text Between Delimiters"[/SIZE]

 

[Partsguy100]

Is the number you want to find always a 7-digit number? If so...

=MID(A1,FIND(CHAR(10)&"205 ",A1)+5,7)

I don't understand your BONUS question. Are you saying the text you posted is not your actual text?

Thanks Rick,
Our item master is just a huge text box with no formatting consistency. The vendor part numbers are always assigned to 'row 205' and vary in length. I would like to have the formula grab the next string of characters until it hits a blank space, and return the info between the end of 205 and the next blank space.

ie.
205 123454-3434-1212

 

[Phuoc]

Try this:

=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("205 ",A1)+3,"")," ",REPT(" ",200)),200))

 

[Partsguy100]

Try this:

=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("205 ",A1)+3,"")," ",REPT(" ",200)),200))

The formula didn't return anything so I played around with it:
=TRIM(LEFT(SUBSTITUTE(REPLACE(E160,1,FIND("205",E160)+3,""),"",REPT(" ",200)),200))

but this returned

6101229 GM DIESEL

<tbody>
</tbody>

 

[Rick Rothstein]

Thanks Rick,
Our item master is just a huge text box with no formatting consistency.

We cannot look for 205 directly (like the formula you are examining does now) to find the value you want because that number could appear within the other numbers appearing before the 205 you want. Is the character in front of the 205 always Line Feed like you showed in your original posting?

 

[Partsguy100]

We cannot look for 205 directly (like the formula you are examining does now) to find the value you want because that number could appear within the other numbers appearing before the 205 you want. Is the character in front of the 205 always Line Feed like you showed in your original posting?

Here is what our long description looks like. There is probably a carriage return at the end of each line but you can see the 000 line is not formatted the same as the other lines. I would just like to return the first string after the 205 number.

000 4425897 EA N
060 TRANSFORMER T4 REP
205 6965020 GM DIESEL
350 END USE F59 PH LOCO HEP ELECTRICAL EQUIPMENT
351 SET-UP DATE 14/03/91

 

[Rick Rothstein]

Give this formula a try...

=MID(LEFT(A1,FIND(" ",A1,FIND(CHAR(10)&"205 ",A1)+5)),FIND(CHAR(10)&"205 ",A1)+5,99)

 

[Partsguy100]

Give this formula a try...

=MID(LEFT(A1,FIND(" ",A1,FIND(CHAR(10)&"205 ",A1)+5)),FIND(CHAR(10)&"205 ",A1)+5,99)

I get a #VALUE ! error when I used this formula