Unanswered

Extract Parts of an Excel Cell Data

(0) Share

Report

Posted on by MDK2nd

Good day,

I have an excel file with 10000 plus rows and need to extract particular cell data. Some cells contain data the is a combination of numbers and letters (12345ABCD). I would like to extract the 12345 to another cell and delete it from the cell containing 12345ABCD. In this case i want to operate in excel sheet and not table.

Thanks in Advance.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (5)

Answers (0)

Highboy 1,183 Super User 2024 Season 1 on at

Like (0)

Report

You could read the cell containing the Nummber/Letter combination
Use Parse text to split it into two variables that you can write to the cells you want.

For the numbers

And for the letters

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report

In order to offer an appropriate solution please share more examples of all possible values in that column.

Are they all of this format and length?

12345ABCD

98765PQRS

11111WXYZ

Was this reply helpful? Yes No
MDK2nd 2 on at

Like (0)

Report

Thanks for the feedback.
The possible values in the column will be of varied lengths with the only constant being the first 4 characters which will always be ABCD and the next 10 numeric values will be assorted/mixed but they will always be 10 numeric values
ABCD0123456789 CHARACTERS
ABCD9012345678 SOMEMORECHARACTERS
ABCD056234789 SOMECHARACTERS
ABCD0129956789 LESSCHARACTERS
I want to a) Check if column B is empty and if so b) pick the ABCD0123456789 part from column C, delete it from Column C and paste it in Column B. Column C should only have the remaining characters.
Hoping this is more clear now.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

Anything that has to be done row by row is not going to work. It will essentially take forever.

im going to give you the Excel workflow, and you will automate this, it will be much faster.

select column B

on the Home menu, far right, left click “Go To” -> Special -> Blanks ‘now all the blank cells in column B should be highlighted and the first blank cell should be active. For the following formula, I am going to assume B2 is the first blank cell in columnB, but instead what you want to do is send keys for =Left({Right},14). So in the case of B2, you would get:

=Left(C2,14)

then send keys {Control}({LShiftKey}({Enter}))

Ctrl + Shift + Enter copied the formula to all selected cells, so to recap

Select Column B

Go to Special -> Blanks

=Left([Cell to the Right],14)

Ctrl + Shift + Enter

Done.

Was this reply helpful? Yes No
XRQ57 206 on at

Like (0)

Report

Hi,
why don't you try with Powerquery?
It will easier to manipulate and one you have build your query, you can use it at any time using the actualisation button.

Was this reply helpful? Yes No