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

#### Swiftkoala

##### New Member
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

### 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
Will you always have a word in A1?

#### jtakw

##### Well-known Member
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)

#### Fluff

##### MrExcel MVP, Moderator
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

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
Do either of the solutions work for you?
If not you need to show some expected results.

#### Swiftkoala

##### New Member

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?

#### Fluff

##### MrExcel MVP, Moderator
If you delete the rows where column A has a country there will be nothing for the formula to return.

#### Swiftkoala

##### New Member
Yes that's what I have done and exactly that happened. Is there any workaround around this?

#### Fluff

##### MrExcel MVP, Moderator
You would need to convert the formulae to values first.

Replies
20
Views
484
Replies
10
Views
190
Replies
5
Views
237
Replies
9
Views
276
Replies
1
Views
56

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.

### Which adblocker are you using?

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

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