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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Will you always have a word in A1?
 
Upvote 0
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)
 
Upvote 0
Solution
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)
 
Upvote 0
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)
 
Upvote 0
Do either of the solutions work for you?
If not you need to show some expected results.
 
Upvote 0
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
 
Upvote 0
If you delete the rows where column A has a country there will be nothing for the formula to return.
 
Upvote 0
Yes that's what I have done and exactly that happened. Is there any workaround around this?
 
Upvote 0
You would need to convert the formulae to values first.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top