Scribd
Upload
31 views

Johnson H Smith Prasad ABC Kumar Abc 123 Zxy Dke 392 Eiek

The document contains examples of formatting bank account numbers by prefixing zeros to make them all 10 characters using the REPT function in Excel, as well as examples of extracting price values from cells located between date values and the text "ABCD". It also provides examples of combining first and last name columns into a single full name column using a formula.

Uploaded by

Krishna APO
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
31 views

Johnson H Smith Prasad ABC Kumar Abc 123 Zxy Dke 392 Eiek

The document contains examples of formatting bank account numbers by prefixing zeros to make them all 10 characters using the REPT function in Excel, as well as examples of extracting price values from cells located between date values and the text "ABCD". It also provides examples of combining first and last name columns into a single full name column using a formula.

Uploaded by

Krishna APO
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 9

Requirements

Name left 5 characters Right 5 characters 4th to 6th char


Johnson H smith
Prasad ABC kumar
ABC 123 ZXY
DKE 392 EIEK

Find out where in the text First and Second Comma starts in the Text.

Names First Comma Second Comma


asdf,diekei,fkdidk 5 12
dkdiekeid,dkeie,firkeid 10 16

Convert the Input the Output using Formula [Text Function]


Input Output
23-Jan-19 15-May-15

Combine Two Columns using a Excel Formula.

First Name Last Name Full name


Paavo Aakarshan Eg--> Paavo, Aakarshan
Pablo Aaron
Pacifico Abaddon
Packard Abba
Paco Abban
Paddy Abbas
Padget Abbott
Padgett Abdel
Padmakar Abdiel
Padraig Abdieso
Padruig Abdul
Upper Case Lower Case Proper Length

Today is Wednesday

aavo, Aakarshan
Input
0905_deal_30468246.xls_x000D_ Output should be
BDB
Claim_x001A__x001B__x0008__x0008_
_x0008__x0010__x000F__x0012_
0905_deal_30468246.xls_x000D_ SCRB-0612-0905_deal_30468246.xlsBDB Claim
BDB
Claim_x001A__x001B__x0008__x0008_
_x0008__x0010__x000F__x0012_
0900_deal_30477960.xls_x000D_ SCRB-0612-0905_deal_30468246.xlsBDB Claim
BDB
Claim_x001A__x001B__x0008__x0008_
_x0008__x0010__x000F__x0012_ SCRB-0612-0900_deal_30477960.xlsBDB Claim

Excel Training Excel Training


Your Answer

SCRB-0612-0905_deal_30468246.xlsBDB Claim

_x000E__x000F__x0017_SCRB-0612-0905_deal_30468246.xls_x000D_BDB Claim_x001A__x001B__x0008__x0008__x000

_x000E__x000F__x0017_SCRB-0612-0900_deal_30477960.xls_x000D_BDB Claim_x001A__x001B__x0008__x0008__x000

Excel Training
x001B__x0008__x0008__x0008__x0010__x000F__x0012_

x001B__x0008__x0008__x0008__x0010__x000F__x0012_
Data Price Sample output
01-Jan-2012 56.451 ABCD 56.451
02-Jan-2012 76.2 ABCD 76.2
03-Jan-2012 45.901556 ABCD 45.901556

>> Kindly develop one formulae for which can populate only the price (which is in between date & "ABCD"). Sample output sho
& "ABCD"). Sample output shown in column D.
Bank Account Number step1 step2 step3
93367 5
88299 5
86914 5
235 3
97546 5
44193 5
76072 5
42 2
96729 5
23206 5
75495 5
97418 5
3511 4
87988 5
52946 5
26603 5
45 2
13880 5
18444 5
1002 4
54546 5
step4
In the Given Data, Bank Account Number should be 10 characters
Eg-> Bank account number 93367 is 5 characters, it should be "0000093367".
Kindly find out a smarter way to prefix zeros for the bank account number.

Hint-->
Text to Columns would not help you.
Try out Excel Function--REPT to add leading zeros.

You might also like