1

Currently I have a log file from restaurant which contains the product name and all.
I have to extract only Product name but not its price which is on same cell.
Report Format 

Product Code | Product Name
00041             Beef Salted Tongue,1000 Yen (excl.tax)
00042             Pork Loin, 980 Yen (excl.tax)


Not that Every product name ends with " , [price][excl.tax]
So is there anyway to remove it from whole column?


I have used =LEFT(A1,(FIND(",",A1,1)-1))
It does help but some of the product contains multiple "," .
For Example : Eggs,Pickles,Sauce, Pepper .
So If I use the above formula, It only gives me Eggs.

Anuj
  • 170
  • 11
  • I believe you need to define some rules of extraction which only you can do best. e.g. I want to extract everything before the last comma in the Product Name column. Or, I want to find all the text that appears before the first number in cell. So my question to you is: what is the universal rule that will satisfy all the Product Names extraction successfully – Bharat Anand Aug 17 '18 at 05:00
  • I want to extract the text from last Commas from left. here in example `Beef Salted Tongue,1000 Yen (excl.tax)` , I want to extract Beef Salted Tongue . So that in the example `Eggs, Pickles,Sauce, pepper, 1000 Yen (excl.tax)`. It will extract Egg, Pickles, Sauce , Pepper – Anuj Aug 17 '18 at 05:07
  • Ok, so if somehow you are able to get the position of last comma in the cell, then its a cake from there? – Bharat Anand Aug 17 '18 at 05:15
  • Yes Very Smooth. + Every Products format is `Beef Salted Tongue,1000 Yen (excl.tax)` . Only some of them contains multiple products. – Anuj Aug 17 '18 at 05:16
  • Ok. Have a look at this one - https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string. Let us know if this solves your problem. In the meantime I'll see if I can post a neat solution using this link's solution – Bharat Anand Aug 17 '18 at 05:19
  • Ran into new problem now. Used the formula you mentioned above, and I got the result partially. But I cant copy paste because I had filtered tab on. So its not skipping the cell.. – Anuj Aug 17 '18 at 05:23
  • Sorry I do not get the issue. Is it not possible for you to remove the filter and then paste the values? Or you do not want this formula to be implemented on the filtered out cells for some reason? – Bharat Anand Aug 17 '18 at 05:27
  • I have 1005 columns,, and about 300 datas have price included, rest dont have price. So i dnt need to use formulas for it, Thats Why had to filtered only the items which had price. – Anuj Aug 17 '18 at 05:30
  • Try this - This does exist in Excel (despite being hidden or not immediately intuitive). After you have selected the data you need to copy into the non-contiguos cells that have been filtered: In Excel 2007 and earlier: Edit-> Go To -> Special -> Visible Cells Only will select the data, then you do a Paste Special Values In Excel 2010 and above: in the Home tab Find and Select -> Go to Special -> Visible Cells only will select the data, then you do a Paste Special Values – Bharat Anand Aug 17 '18 at 05:53

3 Answers3

2

Posting answer as per discussion over the comments:

  1. Use the below link to find the location of last comma in your cell contents.

Excel: last character/string match in a string

  1. To solve the second issue you encountered (pasting the value across filtered rows), refer to the below solution (courtesy TurgutKapisiz)

This does exist in Excel (despite being hidden or not immediately intuitive).

After you have selected the data you need to copy into the non-contiguous cells that have been filtered:

In Excel 2007 and earlier: Edit-> Go To -> Special -> Visible Cells Only will select the data, then you do a Paste Special Values In Excel 2010 and above: in the Home tab Find and Select -> Go to Special -> Visible Cells only will select the data, then you do a Paste Special Values

Bharat Anand
  • 464
  • 1
  • 6
  • 14
1

The required formula is shown in the attached image for your calculation of extraction of required text (click on the Image for clarity).

=LEFT(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789"))-1) //For the data or info @ B2 cell in excel

Output Output

Bhaskar Reddy
  • 171
  • 1
  • 9
  • I like your solution. Its an ingenious way to solve this problem. Maybe you could also add some explanation for the benefit of OP. What I understand is that you are trying to find the position of the first numeral in the cell contents after appending all the numeric characters to the end of the cell text. This is good! – Bharat Anand Aug 17 '18 at 06:04
1

Enter the following Formula in cell B3

=IF(ISERROR(FIND(" Yen ",B2,1)),B2,LEFT(B2,FIND("#",SUBSTITUTE(B2,",","#",LEN(B2)-LEN(SUBSTITUTE(B2,",",""))),1)-1))

Part 1 of the Formula checks the presence of Yen. If there is a possibility that there can be a product with the text Yen the you may replace this part with If(Right(B2,1)=")") or any other unique text that exists only when price of the item is present.

Part 2 of the formula, substitutes comma and compares the length of the text before and after the comma is replaced. It replaces the comma with Has (#), and returns the text to the left of the Hash.

Do let me know if this is what you wanted to do or if require any changes.

Regards,

Vijaykumar Shetye,

Panaji, Goa,

India

Vijaykumar Shetye
  • 44
  • 2