VBA: Copy number from a string of text and insert into cells below

Question

I want to create a makro in Excel which performs - after pressing a button - the below. I attached some dummy data that is formatted like the actual sheet.

enter image description here

There are several data blocks that are seperated by headlines in Sheet 1. From these headlines, I want to get a string of numbers and put it into column S for each line with data below that heading. In line 6 the heading says "2000", thus lines 8-19 should have a "2000" in column S and so on. The number I want to get is always after the word "Monthlyaccount" but the lenght of the number can be different - from 1 to 7 digits. For lines where no data is given (data lines might be recognized by looking if in A is a valid date given) there should just be "ERR" instead of the number.

Can anyone help out?

Thanks so much upfront!

I tried to get the string using =MID but the position is always different. I can recognize which line is a headline but can't bring it on from there... — user1507035
– user1507035, Commented Jun 19, 2013 at 10:17

Kazimierz Jawor · Accepted Answer · 2013-06-19 14:26:16Z

1

Say the string (in cell A1) contains "Monthlyaccount" followed by a blank followed by a number followed by another blank. To extract the number, use:

=--LEFT(MID(A1,FIND("Monthlyaccount",A1)+15,9999),-1+FIND(" ",MID(A1,FIND("Monthlyaccount",A1)+15,9999)))

edited Jun 19, 2013 at 14:26

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

answered Jun 19, 2013 at 13:51

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1507035 Over a year ago

Thanks for the suggestion, but it does not quite work. In this case in outputs the word after the number for me but not the number

Abe Gold · Accepted Answer · 2013-06-19 22:28:03Z

0

This does as requested. Paste the following into S3 and drag down

=IF(AND(ISNUMBER(RIGHT(A2,4)*1),ISNUMBER(RIGHT(A3,4)*1)),S2,IF(ISNUMBER(RIGHT(A3,4)*1),LEFT(RIGHT(A1,LEN(A1)-FIND("Monthlyaccount",A1)-14),FIND(" ",RIGHT(A1,LEN(A1)-FIND("Monthlyaccount",A1)-14))),"ERR"))

edited Jun 19, 2013 at 22:28

answered Jun 19, 2013 at 20:56

Abe Gold

2,38718 silver badges31 bronze badges

1 Comment

user1507035 Over a year ago

It does not look bad as it says ERR in the 3 heading rows but then the cells remain empty, so it does not work completly

Collectives™ on Stack Overflow

VBA: Copy number from a string of text and insert into cells below

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related