formula to delete word that contains number (VBA now required)

[nlyrk0]

Capture.PNG

When the text has a number, the whole word has to be removed, keeping the rest of the text.

current: -SD8 DYNA SLIM 22 15 FOX
final: DYNA SLIM 22 15 FOX

When the initial word contains no number, the word is still there, keeping the rest of the text.

current: -SELENIA K 15W40 1L
end: SELENIA K 15W40 1L

This for the whole column

Can someone help me?

[AliGW]

This can't be done with a formula unless you use a helper column. Do you want me to move this to the VBA section?

[nlyrk0]

I can create an additional column to have the answer, no problem

[AliGW]

OK.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[63falcondude]

With your strings in column A starting with A1, try this in B1:

=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},LEFT(A1,FIND(" ",A1)))),MID(A1,FIND(" ",A1)+1,LEN(A1)),RIGHT(A1,LEN(A1)-1))

or slightly shortened:

=MID(A1,IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},LEFT(A1,FIND(" ",A1)))),FIND(" ",A1)+1,2),LEN(A1))

[nlyrk0]

I was sharing the file. But I'll test first 63falcondude.

[nlyrk0]

2.PNG

Look at the error.

[63falcondude]

Please re-read post #4 and post an Excel workbook, not a picture.

Also, you didn't change all of the references from A1 to G1.

[nlyrk0]

https://mega.nz/#!X89gHaRb!yHBbYQSt2..._cVbLsKEuPrL3M

[63falcondude]

Please re-read post #4 again to see how to upload a sample workbook directly to this site.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[nlyrk0]

https://www.excelforum.com/attachmen...1&d=1522085193

[63falcondude]

Please re-read post #4 again...

The attachment that you shared in post #11:
1) is not an Excel workbook (it's a CSV)
2) is not at all small
3) does not include the desired results

I'll come back once you follow the directions outlined in post #4.

[bebo021999]

Are you working with column B?
Try in D1 then drag down:

Please Login or Register  to view this content.

[nlyrk0]

the text is in column G

[nlyrk0]

It returns me the excel with the formula working, already tested every way and it does not work.

[nlyrk0]

ready. help me

[nlyrk0]

I think this problem I reported earlier is because of my region. Brazil. keyboard portuguese brazil abnt2. how to solve?

[AliGW]

You need to change all references to A1 to G1.

Then you need to change all commas to semi-colons ( , to ; ).

[nlyrk0]

did not work with any of the 3 formulas presented here.

[AliGW]

It's because you have changed things you should not have changed - you need to be more careful.

Try this:

=IF(COUNT(SEARCH({0,1,2,3,4,5,6,7,8,9};LEFT(SUBSTITUTE(TRIM(MID(G1;FIND("-";G1)+1;255));" ";REPT(" ";100));100)));LEFT(G1;FIND(" -";G1))&" "&MID(TRIM(MID(G1;FIND("-";G1)+1;255));SEARCH(" ";TRIM(MID(G1;FIND("-";G1)+1;255)))+1;255);G1)

[nlyrk0]

worked perfectly. You do not know how grateful I am. I was desperate for this job that I have to do.

is it possible to extend this formula to answer beyond the first word, to the second and third? only the first three words. lol

I'm not so good at it.

[AliGW]

I don't think formula solutions are the right way to go if you need to extend this. You should ask for a VBA solution isntead.

Glad I was able to get this formula working for you.

[nlyrk0]

I create another topic or you can transfer this to the correct forum?

[AliGW]

I will move it for you now.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.