If cell starts with letter/contain word return the text to another cell

Swiftkoala

New Member
Joined
Feb 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi All,

so what I'm trying to do is to return the text in A1 to L column, but only if it eithers

a) Starts with the letter (likely most ideal solution)
b) Contain specific words

I think both options might work, however I'm not sure how to proceed.

The catch is:

1) I want to return given text only up to the point where another row starts with the letter. At that point, I would like to have new value mirrored

2)Table is irregular, meaning sometimes I would need to return same value 3x and sometimes 5x

Could you help, please?

Excel3.jpg
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Will you always have a word in A1?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Not sure what your expected results are, may be this.

If you can add a dummy row at the beginning of your data, we can use just 1 formula, otherwise we need 2 like below:

Book3.xlsx
ABC
1BrazilBrazil
225th Feb 2021Brazil
3123Brazil
4456Brazil
5789Brazil
6ColombiaColombia
7456Colombia
8789Colombia
9123Colombia
10258Colombia
11852Colombia
12654Colombia
13753Colombia
14159Colombia
15951Colombia
16DenmarkDenmark
Sheet802
Cell Formulas
RangeFormula
C1C1=IF(ISNUMBER(LEFT(A1)+0),"",A1)
C2:C16C2=IF(ISNUMBER(LEFT(A2)+0),C1,A2)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
On a similar vain I was thinking in L1
Excel Formula:
=IF(ISERROR(LEFT(A1)+0),A1,"")
and L2 copied down
Excel Formula:
=IF(ISERROR(LEFT(A2)+0),A2,L1)
 

Swiftkoala

New Member
Joined
Feb 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Edit as I can't edit original post, sorry for the confusion:

so what I'm trying to do is to return the text in A1 to L column, but only if it eithers

a) Starts with the letter (likely most ideal solution)
b) Contain specific words

it should say:

so what I'm trying to do is to return the text in A column to L column, if the circumstances match the original post (it doesn't have to 1 row)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Do either of the solutions work for you?
If not you need to show some expected results.
 

Swiftkoala

New Member
Joined
Feb 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks, both of you.

=IF(ISERROR(LEFT(A1)+0),A1,"")

It works! There is, however one catch.

It would be great if there would be way of deleting the rows where only the name of countries (with no other data) could be deleted without affecting the data.

Is it possible?

1614369348882.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
If you delete the rows where column A has a country there will be nothing for the formula to return.
 

Swiftkoala

New Member
Joined
Feb 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Yes that's what I have done and exactly that happened. Is there any workaround around this?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You would need to convert the formulae to values first.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,171
Messages
5,640,579
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top