Moving numbers within text string from one cell to another

[Ride The Lightning]

Hi

I have data in Column A: 1And, 3And, 12And etc

I am trying to retrieve the numbers in The cells in column A and move them to column B, so Columns A and B should read: (A) And, And, And (B) 1, 3, 12.

I have tried the "Text to Columns" wizard but I can only move certain parts of the text string and not just the numbers.

Is there a formula to achive this?

Regards

RTL

 

[T. Valko]

Hi

I have data in Column A: 1And, 3And, 12And etc

I am trying to retrieve the numbers in The cells in column A and move them to column B, so Columns A and B should read: (A) And, And, And (B) 1, 3, 12.

I have tried the "Text to Columns" wizard but I can only move certain parts of the text string and not just the numbers.

Is there a formula to achive this?

Regards

RTL

Is the alpha portion of the string ALWAYS the 3 letters And?

How many digits can the number portion be? Any leading zeros?

 

[Ride The Lightning]

Is the alpha portion of the string ALWAYS the 3 letters And?

How many digits can the number portion be? Any leading zeros?

No, it is not always 3 letters it can vary.

The number position can be 3 digits and no leading zeros.

 

[T. Valko]

No, it is not always 3 letters it can vary.

The number position can be 3 digits and no leading zeros.

Try something like this...

Book1
	A	B	C
1	1And	1	And
2	2And	2	And
3	12And	12	And
4	123MoreStuff	123	MoreStuff
Sheet1

This formula entered in B1 and copied down to extract the number portion of the string:

=-LOOKUP(0,-LEFT(A1,{1,2,3}))

This formula entered in C1 and copied down to extract the alpha portion of the string:

=MID(A1,LEN(B1)+1,100)

Adjust the 100 value if the alpha portion of the string is more than 100 characters in length.

 

[Ride The Lightning]

T. Valko, thank you very much again.

 

[T. Valko]

T. Valko, thank you very much again.

You're welcome. Thanks for the feedback!